Why an error on Sumproduct?
 

Hi Team,

was wondering if you could shed some light on this for me.

I have these 3 columns... A , E and P
I use these countif statements to try and set up my Sumproduct....

=COUNTIF($E$2:$E$4000,"MST CSD") --results in a count of 405
=COUNTIF($A$2:$A$4000,V27) --results in a count of 33

Column P has an integer ranging from 1 - 40

I want to add colum P if E="MST CSD" and A = 18 (the value in V27)

so I use

=SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000))

But this results in an #N/A

I know by using the Data filter that there are 8 occurances that meet the
conditions and should be adding together to give a result of 141

any ideas of what I have done wrong?

Why an error on Sumproduct?
 

=SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000))

Your formula should work but a "cleaner" way to write it would be:

=SUMPRODUCT(--($A$2:$A$4000=V27),--($E$2:$E$4000="MST CSD"),$P$2:$P$4000)

As far as the #N/A error, do you have any #N/A errors in any of the ranges?


--
Biff
Microsoft Excel MVP


"Madduck" wrote in message
...
Hi Team,

was wondering if you could shed some light on this for me.

I have these 3 columns... A , E and P
I use these countif statements to try and set up my Sumproduct....

=COUNTIF($E$2:$E$4000,"MST CSD") --results in a count of 405
=COUNTIF($A$2:$A$4000,V27) --results in a count of 33

Column P has an integer ranging from 1 - 40

I want to add colum P if E="MST CSD" and A = 18 (the value in V27)

so I use

=SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000))

But this results in an #N/A

I know by using the Data filter that there are 8 occurances that meet the
conditions and should be adding together to give a result of 141

any ideas of what I have done wrong?

Why an error on Sumproduct?
 

works okay for me except when there is #N/A error in the data somewhere.


"Madduck" wrote:

Hi Team,

was wondering if you could shed some light on this for me.

I have these 3 columns... A , E and P
I use these countif statements to try and set up my Sumproduct....

=COUNTIF($E$2:$E$4000,"MST CSD") --results in a count of 405
=COUNTIF($A$2:$A$4000,V27) --results in a count of 33

Column P has an integer ranging from 1 - 40

I want to add colum P if E="MST CSD" and A = 18 (the value in V27)

so I use

=SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000))

But this results in an #N/A

I know by using the Data filter that there are 8 occurances that meet the
conditions and should be adding together to give a result of 141

any ideas of what I have done wrong?

Why an error on Sumproduct?
 

Thank you both... there was a sneaky little N/a I missed on checking .<



thanks again ......



"JMB" wrote:

works okay for me except when there is #N/A error in the data somewhere.


"Madduck" wrote:

Hi Team,

was wondering if you could shed some light on this for me.

I have these 3 columns... A , E and P
I use these countif statements to try and set up my Sumproduct....

=COUNTIF($E$2:$E$4000,"MST CSD") --results in a count of 405
=COUNTIF($A$2:$A$4000,V27) --results in a count of 33

Column P has an integer ranging from 1 - 40

I want to add colum P if E="MST CSD" and A = 18 (the value in V27)

so I use

=SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000))

But this results in an #N/A

I know by using the Data filter that there are 8 occurances that meet the
conditions and should be adding together to give a result of 141

any ideas of what I have done wrong?