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?
|