#VALUE using SUMPRODUCT function

  • Thread starter Thread starter Brooks W.
  • Start date Start date
B

Brooks W.

I am using the following formula to check for 2 criteria

=SUMPRODUCT(--('Intl Detail'!A2:A737=10950796)*('Intl Detail'!W2:W737="Y"))

This gives me the correct count for the information I have.

However, on a new spreadsheet (this is on a monthly report we get) I get the
#VALUE message for this formula

=SUMPRODUCT(--('Intl Detail'!A2:A898=10950796)*('Intl Detail'!W2:W898="Y"))

The only difference is the range specified.

This happens on my office machine as well as my home machine. Both are using
Excel 2003.
 
Does one of the cells in either A738:A898 or W738:898 have a #VALUE in it?
That woudl cause SUMPRODUCT to return this error.
 
OH My GOSH. Thanks. I guess I just needed to think differently. I found the
problem and fixed the error. Thank You!
 
Firstly, the -- in your formula is redundant.

Secondly, it looks as though you have a formula returning an error result in
the data.
 
Back
Top