#VALUE using SUMPRODUCT function
 

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.
--
Brooks W.

#VALUE using SUMPRODUCT function
 

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." wrote:

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.
--
Brooks W.

#VALUE using SUMPRODUCT function
 

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


"Sean Timmons" wrote:

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." wrote:

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.
--
Brooks W.

#VALUE using SUMPRODUCT function
 

Firstly, the -- in your formula is redundant.

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

--
__________________________________
HTH

Bob

"Brooks W." wrote in message
...
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.
--
Brooks W.