#N/A error in sumproduct

[Joe M.]

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!D23000 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.

 

[Fred Smith]

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.

 

[Simon Lloyd]

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

Try using error check or autofilter to check out you data in D23000
with
lot of data it can be easy to miss one error. If even one cell is
error i
can error out the sumproduct
-
If this helps, please remember to click yes

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[David Biddulph]

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

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"))

Try using error check or autofilter to check out you data in D23000,
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.

--
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/showthread.php?t=161166

Microsoft Office Help

 

[Joe M.]

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

Joe M.

 

[Fred Smith]

Glad to help. Thanks for the feedback.

Fred.

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

Joe M.