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!

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

Hi ramananhrm

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

Could anybody please help?

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.

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?

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

Could anybody please help?

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?

Formula:

Please Login or Register  to view this content.

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!