COUNTIFS with Date Range

[whizbee]

I am trying to use COUNTIFS to count the number of records within specified date ranges. I am using the following formula:

=COUNTIFS(Data!$I:$I,">="&B11,Data!$I:$I,"<="&B12)

"I" is the column with the dates
B11 points to the start date, in this case 7/1/2010
B12 points to the end date, in this case 7/31/2010

This formula is returning 176 records, but when I filter column I for July 2010, I receive 179 records.

Any ideas on the problem?

[Ron Coderre]

Try this to spot the difference...
• Filter your data
• Put this formula somewhere on the first filtered row on the Data sheet...Assuming Z5
Z5: =COUNTIFS(I5,">="&$B$11,I5,"<="&$B$12)
(Note: if cells $B$11 and $B$12 are on a different sheet, you'll need to reference them properly)

Copy that formula down into each visible cell.

You're interested in the cells that return 0 but are visible in the filtered list

Does that help?

[whizbee]

I don't understand what is meant by "Assuming Z5".

[Ron Coderre]

It means that I just pretended that the first visible filtered data was in Row_5 and that cell Z5 was a convenient place to put the formula I posted.
In your scenario, select a convenient cell on the first visible filtered data row of your data and put the formula there. Remember to change the references so they point to the cells on that row.

[whizbee]

Done. The zeros are occuring on the three 7/31/2010 dates. What now?

[Tony Valko]

See if this helps...

Let's assume the entire range of data is I5:I100.

=SUMPRODUCT(SUBTOTAL(2,OFFSET(Data!I5,ROW(Data!I5:I100)-ROW(Data!I5),0)),--(Data!I5:I100>=B11),--(Data!I5:I100<=B12))

You should avoid using entire columns as range references in the SUMPRODUCT function.

[whizbee]

I tried this:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(Data!I1,ROW(Data!I1:I1000)-ROW(Data!I1),0)),--(Data!I1:I1000>=B11),--(Data!I1:I1000<=B12))

It still returns 176.

Why should entire column references be avoided? The data will be regularly updated and added to, so entire columns would be most convenient to avoid needing to update the formulas.

[whizbee]

If I re-type the date of 7/31/2010, it will work, or if I add 11:59:59 PM to the date I am pointing to, it will work. The problem is that many users will use this, and this is not a good work around. Additionally, the 11:59:59 PM will not automatically copy to new cells, so it has to be manually added to each end date.

The report is an export from our software. The dates evidently have time stamps embedded.

Any solutions?

[Tony Valko]

.

The report is an export from our software. The dates evidently have time stamps embedded.

Try this version:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(Data!I1,ROW(Data!I1:I1000)-ROW(Data!I1),0)),--(INT(Data!I1:I1000)>=B11),--(INT(Data!I1:I1000)<=B12))

I'm assuming that you want the count to be accurate when you apply a filter:

when I filter column I for July 2010

You should avoid using entire columns as range references with the SUMPRODUCT function (or any array formula) because it's very inefficient.

The SUMPRODUCT function (and array formulas) will evaluate EVERY cell referenced. Based on the formula you just posted there are over 1 million empty cells in column I that would be evaluated which is a waste of resources and can cause overall file performance to slow down.

If data will be added frequently then use a dynamic defined range that will automatically adjust as data is added.

The dynamic range would be defined as...

Goto the Formulas tab>Formulas>Define name
Name: Dates
Refers to: =Data!$I$2:INDEX(Data!$I:$I,MATCH(1E100,Data!$I:$I))

Note that the dynamic range should not include a text column header. Only define the range that contains the actual data that needs to be calculated.

Then the formula would become:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(INDEX(Dates,1),ROW(Dates)-MIN(ROW(Dates)),0)),--(INT(Dates)>=B11),--(INT(Dates)<=B12))

[Ron Coderre]

Done. The zeros are occuring on the three 7/31/2010 dates. What now?

Try this variation:

Please Login or Register  to view this content.

Does that solve the problem?

[whizbee]

That did work Ron! Thank you.

[Tony Valko]

That did work Ron! Thank you.

Hmmm...

I must be missing something.

when I filter column I for July 2010

If you apply an autofilter to the data then a COUNTIFS formula will calculate on the entire range, not just the filtered range.

[whizbee]

Tony,

When I am filtering, I am filtering to check my formula, not to have the formula use only the filtered records.

[Tony Valko]

Ok, got it.

Thanks for the clarification.

[rpharrington]

This helped me too! Thanks again, Ron!