SUMPRODUCT counts 1 (Jan) even when cell is blank

**zackary68** · 08-06-2008, 12:24 PM

Not sure if this is the right place, however I am hours into researching and attempting to format the columns and still getting the same result.

This formula works for all other months except for Jan.

When I get to the line for January, it gives me output of ALL cells that are empty. (11000+)

=SUMPRODUCT(--(MONTH('WAREHOUSE LOCATIONS'!C$2:C$11612)=1))

At first I thought it was just formatting, excel rounding up or something; however there seems to be something specific about the value "1" when attempting this.

Any suggestions or help would be greatly appreciated.

Zack

**NBVC** · 08-06-2008, 01:09 PM

Try:

=SUMPRODUCT(--(MONTH('WAREHOUSE LOCATIONS'!C$2:C$11612)=1),--(WAREHOUSE LOCATIONS'!C$2:C$11612<>""))

**zackary68** · 08-06-2008, 01:58 PM

Thank you for the help.

Regrettably I get an error message referring to Array2. Excel help has highlighted the word LOCATIONS for some reason.

=SUMPRODUCT(--(MONTH('WAREHOUSE LOCATIONS'!C$2:C$11612)=2),--(WAREHOUSE LOCATIONS'!C$2:C$11612<>""))

Is there a reason you might know of that would keep this from working for Jan

=SUMPRODUCT(--(MONTH('WAREHOUSE LOCATIONS'!C$2:C$11612)=1))

when

=SUMPRODUCT(--(MONTH('WAREHOUSE LOCATIONS'!C$2:C$11612)=12))

works for Dec?

Zack

**NBVC** · 08-06-2008, 02:00 PM

Looks like I missed an apostrophe before the sheetname...

Try again with:

=SUMPRODUCT(--(MONTH('WAREHOUSE LOCATIONS'!C$2:C$11612)=1),--('WAREHOUSE LOCATIONS'!C$2:C$11612<>""))

Excel sees blank cells as Jan/0/1900 when dealing with dates...so it assumes all your blank cells are the month of January... so you have to exclude the blanks using the 2nd condition in the formula.