Basic SUMPRODUCT formula question

**jazzper** · 09-16-2008, 01:23 PM

After rooting around a bit and looking at some answers given by the folks here who know a lot more about using SUMPRODUCT than I do, I thought I had the correct syntax for what I'm trying to do -- but that is obviously not the case!

I have a spreadsheet with a "C" column of departments and a "G" column with dates in the format m/dd/yyyy. In the example below, I'm trying to count the number of instances where the "G" column has "1-HR" in it and the date begins with "1/" for January.

=SUMPRODUCT((DATA!C2:C500="1-HR")*(DATA!G2:G500="1/*"))

I get zeros for everything.
Am I'm missing something obvious????

thanks!

**NBVC** · 09-16-2008, 01:29 PM

If you have entered true dates, then you have to break out the Month number a different way...

Try:

=SUMPRODUCT((data!C2:C500="1-HR")*(MONTH(data!G2:G500=1))*(data!G2:G500<>""))

The last argument, (data!G2:G500<>""), is to ensure it doesn't pick up blank cells as January.

**Ron Coderre** · 09-16-2008, 01:30 PM

Am I'm missing something obvious????

Actually, it's not quite so obvious.
Excel calculates on the contents of a cell....not necessarily the displayed
value. Also, to Excel, dates are represented as the number of days
since 31-Dec-1899 (but it can make it look like the cell contents
are a date).
1 = 01/01/1900
39,707 = 09/16/2008

Try this:

Please Login or Register  to view this content.

[EDITED TO ADD THIS FORMULA]
or....if there may be text in DATA!G2:G500
try this variation to avoid a #VALUE! error:

Please Login or Register  to view this content.

Is that something you can work with?

**jazzper** · 09-16-2008, 02:49 PM

Awesome....you guys are great!!
Just have to rest a bit from banging my head against the wall on this one as I was definitely not aware of the date issue-