Sumproduct syntax

[neodjandre]

I am trying to sumproduct if my column has "j" flag and if the sum does not equal to 0.

=SUMPRODUCT(($E$9:$E$289="j")*--(M$9:M$289)<>0)

this doesnt work as expected. any ideas please?

thanks
Andrew

 

[Domenic]

Try...

=SUMPRODUCT(($E$9:$E$289="j")*(M$9:M$289<>0))

Or, using the comma styntax...

=SUMPRODUCT(--($E$9:$E$289="j"),--(M$9:M$289<>0))

Hope this helps!

 

[jtakw]

Hi,

Solution above will provide you the "Count" of Column E="j" and respective Column M<>0
So if you only want the Count, you can just use COUNTIFS

If you actually want the SUM, then I don't see the need to check for 0 (zero)
And you can just use SUMIF

Book1
	E	J	M
1		19
2		19
3		2
4		19
11	j		9
12	j		10
13	j		0
Sheet209

Cell Formulas
Range	Formula
J1	=SUMPRODUCT(($E$9:$E$289="j")*(M$9:M$289<>0)*M$9:M$289)
J2	=SUMPRODUCT(($E$9:$E$289="j")*M$9:M$289)
J3	=COUNTIFS($E$9:$E$289,"j",$M$9:$M$289,"<>0")
J4	=SUMIF($E$9:$E$289,"j",$M$9:$M$289)