# Count the number of times a date appears in a range

1. ## Count the number of times a date appears in a range

Hi,

I would like to count the number of times a specific date appears in a range and only return a value if the result is not 0.

Example data:

A1 B1 C1 D1
01/01/2013 05/01/2013 01/01/2013 02/01/2013

So based on the above data, the formula would look at cell c1 and count how many times the date specified occurs between A1 and B1. Which would return a value of 1.

I have tried the following: =IF(COUNTIFS(\$A\$1:\$B\$1, ">="&C\$1)=0,"",COUNTIFS(\$A\$1:\$B\$1, ">="&C\$1)) but the problem is it counts the date twice returning a value of 2. Removing the > symbol fixes it but then if asked to look at cell D1 it doesn't return a value.

Could someone please help?

Thanks in advance!

2. ## Re: Count the number of times a date appears in a range

Apologies, the example data hasn't came across properly. It's as follows:

A1 = 01/01/2013
B1 = 05/01/2013
C1 = 01/01/2013
D1 = 02/01/2013

3. ## Re: Count the number of times a date appears in a range

Something like this?

4. ## Re: Count the number of times a date appears in a range

Hi ramananhrm

The date range to look in would always be between two columns. Columns A & B in this case

5. ## Re: Count the number of times a date appears in a range

Could anybody please help?

6. ## Re: Count the number of times a date appears in a range

Hi and welcome to the forum.

Be sure that best way to describe your problem is to upload a sample workbook.

Be sure that all sensitive data removed, showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that.

To attach a small sample workbook.

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

View Pic

7. ## Re: Count the number of times a date appears in a range

welcome to the forum, uhtfgy. your profile shows MS-Off Ver of Excel 2003 but you suggested COUNTIFS. that is only available in Excel 2007 & above. do change your profile to help us help you better. if you didn't mention COUNTIFS, we would have assumed you couldn't use that.

so C1 must be between A1 & B1 to show as 1, & 0 if that's not the case? why would there be a case where you need to count the number of times it appear? it can only appear once right? if that's correct, then try:
=COUNTIFS(C\$1,">="&\$A\$1,C\$1,"<="&\$B\$1)

otherwise, do let us know what scenario does it happen where more than 1 count appears

8. ## Re: Count the number of times a date appears in a range

Hi benishiryo,

Thank you.

I have attached a sample workbook and amended my office version.

Customer table = work planned
Work load table = summary

What I'm trying to do in the work load table is count how many customers we are working on each day by looking at the start and end days (and days in between) for all customers. I have included expected outcomes below the workload table.

Thank you.

9. ## Re: Count the number of times a date appears in a range

in H6:
=COUNTIFS(\$B\$2:\$B\$9,"<="&H5,\$C\$2:\$C\$9,">="&H5)

10. ## Re: Count the number of times a date appears in a range

Or this:

=SUMPRODUCT((\$B\$2:\$B\$9<=H5)*((\$C\$2:\$C\$9>=H5)))

11. ## Re: Count the number of times a date appears in a range

Thank you so much - they both work!!

You guys are superstars

Could I get the same outcome by having the range in one cell e.g. 01/10/13 - 05/10/13 in the same cell instead of having the from and to dates in different cells?

12. ## Re: Count the number of times a date appears in a range

You're welcome and thank you for your feedback! Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

13. ## Re: Count the number of times a date appears in a range

Originally Posted by uhtfgy
Could I get the same outcome by having the range in one cell e.g. 01/10/13 - 05/10/13 in the same cell instead of having the from and to dates in different cells?
not that you can't, but putting Start Date & End Date in 2 cells would be more ideal. say 1Oct13 now in H5 & 5Oct13 in I5, then:
=SUMPRODUCT(COUNTIFS(\$B\$2:\$B\$9,"<="&ROW(INDIRECT(H5&":"&I5)),\$C\$2:\$C\$9,">="&ROW(INDIRECT(H5&":"&I5))))

14. ## Re: Count the number of times a date appears in a range

Originally Posted by benishiryo
not that you can't, but putting Start Date & End Date in 2 cells would be more ideal. say 1Oct13 now in H5 & 5Oct13 in I5, then:
=SUMPRODUCT(COUNTIFS(\$B\$2:\$B\$9,"<="&ROW(INDIRECT(H5&":"&I5)),\$C\$2:\$C\$9,">="&ROW(INDIRECT(H5&":"&I5))))

Hi Benishiryo,

Thank you, but I'm not sure what's happening in that bit of code. I meant having the data set in the same field, so the dates in B2 and C2 would be in one cell instead of in two, and compare that to the date in H5.

On a separate note, I have tried using both the countifs and sumproduct variations suggested above but set it to look at another sheet instead for the date range (columns B and C) and the original sheet for the date to check against (cell H5) but I can't seem to get it to work:

=SUMPRODUCT(('Sheet2'!\$B\$2:\$B\$9<=H5)*(('Sheet2'!\$C\$2:\$C\$9>=H5)))

=COUNTIFS('Sheet2'!\$B\$2:\$B\$9,"<="&H5,\$C\$2:\$C\$9,">="&H5)

Any ideas please?

Many Thanks

15. ## Re: Count the number of times a date appears in a range

Could anybody please help?

16. ## Re: Count the number of times a date appears in a range

If formulas provided to you do not work in your new setting, upload a spreadsheet with a formula to show what is not working. Without seeing your data set it would be rather difficult to tell what needs to be done.

17. ## Re: Count the number of times a date appears in a range

I have attached a sample workbook - it's the same as before only the work load table is on a separate sheet.

Customer table = work planned
Work load table = summary

What I'm trying to do in the work load table is count how many customers we are working on each day by looking at the start and end days (and days in between) for all customers. I have included expected outcomes below the workload table.

Both

=COUNTIFS(\$B\$2:\$B\$9,"<="&H5,\$C\$2:\$C\$9,">="&H5)

and

=SUMPRODUCT((\$B\$2:\$B\$9<=H5)*((\$C\$2:\$C\$9>=H5)))

work when the work load table is on the same sheet. How can I change the code to look in Sheet 1 instead for the customer dates?

18. ## Re: Count the number of times a date appears in a range

Formula:
`Please Login or Register  to view this content.`

19. ## Re: Count the number of times a date appears in a range

Here is also COUNTIFS with Sheet1 references.

=COUNTIFS(Sheet1!\$B\$2:\$B\$9,"<="&H5,Sheet1!\$C\$2:\$C\$9,">="&H5)

20. ## Re: Count the number of times a date appears in a range

Thank you so much to both of you!!

I've realised where I was going wrong, and I hate to admit my very stupid mistake, but I was referencing the wrong sheet!!!

Thank you again!

21. ## Re: Count the number of times a date appears in a range

[QUOTE=uhtfgy;3428999]Hi,

I would like to count the number of times a specific date appears in a range and only return a value if the result is not 0.

Example data:

A1 B1 C1 D1
01/01/2013 05/01/2013 01/01/2013 02/01/2013

So based on the above data, the formula would look at cell c1 and count how many times the date specified occurs between A1 and B1. Which would return a value of 1.

Try this instead:
Let's say, go to cell A3 and type the date you would like to find, for example 01/01/2013.
1. then go to A4 cell and type this: = countif(A1:D1, A3)
and voila...it already counts the number of times the date from A3 appears from cells A1 to D1.

I do hope this will help you.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1