COUNTIF vs COUNTIFS vs SUMPRODUCT

**FASTiger** · 05-27-2013, 10:39 PM

So, I'm trying to count of the number of occurrences of text that happen between two dates among multiple sheets in a workbook, and I cannot get the correct number. I have dates in the E column of each worksheet, and the text I want to count is in column F. For the dates I want to limit, I have them in cells E5 and E6 on the same sheet as the formulas. I currently have the E5/E6 cells dated 01-01-05 and 28-05-13 (there are no dates that go back to 2005, I'm doing this to test the formulas for accurate results before I start limiting).

If I use a COUNTIF, it returns 15. (expected)

=COUNTIF(Sheet2!$F:$F,text)

If I try to use a COUNTIFS to limit the date, it returns 0 even if I don't put the end date.

=COUNTIFS(Sheet2!$F:$F,text,Sheet2!$E:$E,">="&$E$5)

If I try a SUMPRODUCT from the start date, it returns the expected number if I don't put the end date. Which will return 0 if I do.

=SUMPRODUCT((Sheet2!$F$3:$F$100 = text)*(Sheet2!$E3:$E$100 >= $E$5))-SUMPRODUCT((Sheet2!$F$3:$F$100 = text)*(Sheet2!$E3:$E$100 > $E$6))

Is there something I'm missing that would cause both the COUNTIFS and SUMPRODUCT functions to return incorrect values? Any help is appreciated, thanks!

**Jonmo1** · 05-27-2013, 11:15 PM

Which column contains the dates?
Your countifs is looking at E but sumproduct is looking at F.

Are your dates really dates?
Use =ISNUMBER(E3) to test
If its true its a real date

**FASTiger** · 05-27-2013, 11:23 PM

Good catch on the SUMPRODUCT, although they are correct in my spreadsheet. Column E has the dates.

Oddly enough, some of the dates are not correctly being identified as numbers. Is there a way to force them to be read as numbers in the formulas?

**FDibbins** · 05-28-2013, 12:34 AM

The only way your dates would not be regognised as dates/numbers is if there are NOT dates/numbers, but text that looks like a date. test with =isnumber(cell_ref)

**FASTiger** · 05-28-2013, 01:44 AM

Yeah, it looks like a bunch of dates were being recognized as text instead of numbers. Is there a way to force a formula to read it as a date, though?

I mean, I can go through and use something like DATEVALUE and manually change each date that needs it, but the spreadsheet is edited by several people. If someone ends up putting dates in as text, then these formulas won't work...

Edit: Actually, it looks like I can just use Data Validation to force those columns to only accept numbers. That should fix things!