#N/A error in sumproduct
 

Here is my formula:
=SUMPRODUCT((Data!$E$2:$E$3000=$A$1)*(Data!$C$2:$C $3000=$A3)*(Data!$D$2:$D$3000="N"))
If I remove the last segment *(Data!$D$2:$D$3000="N") then I get a result.
When it's included I get #N/A. In Data!D2:D3000 is the result of an =if that
results in Y or N. I also tried changing the =if to 1 or 0 with the same
result.
Can someone help?
Thanks!
Joe M.

#N/A error in sumproduct
 

Somewhere in your D column, you have a cell with #N/A in it. Try turning on
Autofilter. It will show you the different contents you have in the cells in
Column D, and point you to the ones which aren't Y or N

Regards
Fred.

"Joe M." wrote in message
...
Here is my formula:
=SUMPRODUCT((Data!$E$2:$E$3000=$A$1)*(Data!$C$2:$C $3000=$A3)*(Data!$D$2:$D$3000="N"))
If I remove the last segment *(Data!$D$2:$D$3000="N") then I get a result.
When it's included I get #N/A. In Data!D2:D3000 is the result of an =if
that
results in Y or N. I also tried changing the =if to 1 or 0 with the same
result.
Can someone help?
Thanks!
Joe M.

#N/A error in sumproduct
 

What type of data do you have?, maybe you need to coeherse the data
using the double unary like this
=SUMPRODUCT(--(Data!$E$2:$E$3000=$A$1)*(Data!$C$2:$C$3000=$A3)*( Data!$D$2:$D$3000="N"))

Paul C;582448 Wrote:
Try using error check or autofilter to check out you data in D2:D3000,
with a
lot of data it can be easy to miss one error. If even one cell is a
error it
can error out the sumproduct.
--
If this helps, please remember to click yes.


"Joe M." wrote:

Here is my formula:

=SUMPRODUCT((Data!$E$2:$E$3000=$A$1)*(Data!$C$2:$C $3000=$A3)*(Data!$D$2:$D$3000="N"))
If I remove the last segment *(Data!$D$2:$D$3000="N") then I get a
result.
When it's included I get #N/A. In Data!D2:D3000 is the result of an
=if that
results in Y or N. I also tried changing the =if to 1 or 0 with the
same
result.
Can someone help?
Thanks!
Joe M.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161166

Microsoft Office Help

#N/A error in sumproduct
 

You don't need a double unary minus in that situation, Simon. Any
arithmetic operation will coerce a boolean to a number, and you've got
multiplications that will do that, so the double unary minus adds nothing to
the functionality in that formula.

As Paul pointed out, it sounds as if the problem is that some cell in the
Data!$D$2:$D$3000 range contains #N/A!
--
David Biddulph

"Simon Lloyd" wrote in message
...

What type of data do you have?, maybe you need to coeherse the data
using the double unary like this
=SUMPRODUCT(--(Data!$E$2:$E$3000=$A$1)*(Data!$C$2:$C$3000=$A3)*( Data!$D$2:$D$3000="N"))

Paul C;582448 Wrote:
Try using error check or autofilter to check out you data in D2:D3000,
with a
lot of data it can be easy to miss one error. If even one cell is a
error it
can error out the sumproduct.
--
If this helps, please remember to click yes.


"Joe M." wrote:

Here is my formula:

=SUMPRODUCT((Data!$E$2:$E$3000=$A$1)*(Data!$C$2:$C $3000=$A3)*(Data!$D$2:$D$3000="N"))
If I remove the last segment *(Data!$D$2:$D$3000="N") then I get a
result.
When it's included I get #N/A. In Data!D2:D3000 is the result of an
=if that
results in Y or N. I also tried changing the =if to 1 or 0 with the
same
result.
Can someone help?
Thanks!
Joe M.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=161166

Microsoft Office Help

#N/A error in sumproduct
 

Yes my Data!D column contained some invalid data with #N/A. Thanks for your
help!

Joe M.

"Joe M." wrote:

Here is my formula:
=SUMPRODUCT((Data!$E$2:$E$3000=$A$1)*(Data!$C$2:$C $3000=$A3)*(Data!$D$2:$D$3000="N"))
If I remove the last segment *(Data!$D$2:$D$3000="N") then I get a result.
When it's included I get #N/A. In Data!D2:D3000 is the result of an =if that
results in Y or N. I also tried changing the =if to 1 or 0 with the same
result.
Can someone help?
Thanks!
Joe M.

#N/A error in sumproduct
 

Glad to help. Thanks for the feedback.

Fred.

"Joe M." wrote in message
...
Yes my Data!D column contained some invalid data with #N/A. Thanks for
your
help!

Joe M.

"Joe M." wrote:

Here is my formula:
=SUMPRODUCT((Data!$E$2:$E$3000=$A$1)*(Data!$C$2:$C $3000=$A3)*(Data!$D$2:$D$3000="N"))
If I remove the last segment *(Data!$D$2:$D$3000="N") then I get a
result.
When it's included I get #N/A. In Data!D2:D3000 is the result of an =if
that
results in Y or N. I also tried changing the =if to 1 or 0 with the same
result.
Can someone help?
Thanks!
Joe M.