Cant work out the correct formula to use

[jwardlow84]

Hi all,

I hope someone is able to help me with my issue as I am scratching my head on the correct formula to use. I am trying to make a spreadsheet that can keep track of my freelance invoices. In one column I have the date in dd/mm/yyyy format. In another column I have whether a per diem (daily allowance) needs to be claimed. This is either just a simple Yes or No in a drop down tab.

What I would like to achieve is in a separate table if I input the date range, it will determine how many per diems should be paid and make total in separate column for whats owed.

Thanks in advance for any assistance that can be offered.

[Glenn Kennedy]

Hi there... and welcome to the Excel Forum.Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Nisha Dhawan]

Hello there, Do you mean if you have 27/04/15 in column A1 and Yes/No in Cell B1.

Now if you enter the date range say for 27/04/15 to 30/04/15 so you should get to know how many Yes/No irrespective of these dates?

If yes, then you can use Countifs to get the count, if wanted to pick the sum of the daily allowance, then can use SUMIFS.

Please confirm, if this is the case.

[jwardlow84]

Hi Nisha,

Yes so on column 1 I have a range of dates and in column 2 I have whether a per diem has been accrued. So what I am trying to work out is how many yes/no there has been for a selected date range.

So CountIF is the formula I should be using.

@Glenn I have uploaded an example xls for you to see. So as you can see there is a separate table to the right of the main info. I have two cells to enter the date range. Then using DATEDIF formula to count how many days*my contract rate to work out how much is to be invoiced. So all I need to work out is how many per diems "yes" have been accrued for that selected date range.

Thanks for you help.

James

[Glenn Kennedy]

Hi there. Try this.

=COUNTIFS(A:A,">="&$H$2,A:A,"<="&$H$3,C:C,"Yes")

[Glenn Kennedy]

I have just noticed that your thread title isn't very meaningful. Please change it to something that conveys what you want (think in terms of Google searches).

[jwardlow84]

Hi Glenn,

That formula was perfect. Once I had edited with the actual cells in use it works. Sorry about the thread title. I just couldn't work out a suitable title to convey what I was actually trying to achieve.

Thanks Once again.

[Tony Valko]

I just couldn't work out a suitable title to convey what I was actually trying to achieve.

How about this...

Count dates within a range that meet a condition

[Pepe Le Mokko]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Nisha Dhawan]

Hi jwardlow,

It seems that you have got the solution for your query so would request you to please mark this thread as SOLVED and say thanks to people who helped you by adding *Add Reputation.

@Je suis Charlie - Sorry for posting it as the user has already got the answer for the query so there is now no need to make the required changes (as per my thinking) instead that we can ask OP to mark this thread as SOLVED now.