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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top