#VALUE using SUMPRODUCT function

[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.

 

[Sean Timmons]

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.

 

[Brooks W.]

OH My GOSH. Thanks. I guess I just needed to think differently. I found the
problem and fixed the error. Thank You!

 

[Bob Phillips]

Firstly, the -- in your formula is redundant.

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