Count cells based on date range

**The_Oakster** · 07-14-2009, 05:13 PM

I have searched for the answer to this all over today and although I have seen somethings that may help me, I dont understand them or they dont quite cover what I need.

I have a worksheet called Test.

It has 3 columns.

Column A contains only dates in the DD/MM/YYYY format.
Column B may have text in the cells or it can be empty.
Column C may have text in the cells or it can be empty.

I have another worksheet called Totals.

This will show the results of a search based on certain criteria.

A1 is where the first 'from' date criteria is entered.
A2 is where the second 'to' date criteria is entered.

In A5 I have entered the correct formula to count how many cells have the dates that fit the criteria in A1 and A2. So if I type 01/01/2009 in A1 and 01/02/2009 in A2, it returns the numbers of cells that have a date between those two dates.

What I now need is the formula that will count the numbers of cells with text in either column B or column C that match the date criteria.

So A5 will have the total cells between the dates entered (For example it may return 9), A6 will say how many of those 9 have text in Column B and A7 will say how many of those 9 have text in column C.

It is possible for all 9 to have text in column B and column C so A5 could show 9, A6 will show 9 and A7 will show 9, but that doesn't matter.

Is this possible?

**Paul** · 07-14-2009, 05:28 PM

Hi Oakster,

A5: =SUMPRODUCT((Test!A:A>=A1)*(Test!A:A<=A2))
A6: =SUMPRODUCT((Test!A:A>=A1)*(Test!A:A<=A2)*(Test!B:B<>""))
A7: =SUMPRODUCT((Test!A:A>=A1)*(Test!A:A<=A2)*(Test!C:C<>""))

**ConneXionLost** · 07-14-2009, 05:28 PM

Hi Oakster,

Try something like this:

Please Login or Register  to view this content.

Substitute Test!C:C for Test!B:B where required

Cheers,

**The_Oakster** · 07-14-2009, 06:15 PM

All I get with these are #NUM! errors

**Paul** · 07-14-2009, 06:17 PM

Ahh, are you using Excel 2003 or older? If so, you can't use SUMPRODUCT on a full column. Change them from A:A, B:B and C:C to something like A1:A1000, B1:B1000 and C1:C1000 (basically to a row below your last row of data).

In the sumproduct formula, all the ranges must be the same size. So you can't use A1:A100 and compare it to B1:B200, etc..

**The_Oakster** · 07-14-2009, 06:22 PM

Haha, I just solved that myself!

Does it make a difference if I use the maximum number of cells or will that cause some slow down?

**Paul** · 07-14-2009, 06:24 PM

I wouldn't use the max cells (A1:A65536). I'd pick a row number that you are certain will never be reached, but not ridiculously large. With just a few sumproduct formulas using the max number of rows, you probably wouldn't notice it too much, but if you started using many of them there would certainly be additional overhead.