# 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.

Thanks in advance!   Register To Reply

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  Register To Reply

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

Something like this?  Register To Reply

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  Register To Reply

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

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  Register To Reply

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  Register To Reply

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

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.  Register To Reply

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)  Register To Reply

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)))  Register To Reply

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?  Register To Reply

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

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))))  Register To Reply

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)

Many Thanks  Register To Reply

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

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.  Register To Reply

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

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?  Register To Reply

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

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

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)  Register To Reply

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!   Register To Reply

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.

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.   Register To Reply