Formula Puzzle

[knightofni]

Hello guys,

I was wondering if anyone can explain why the following formulas, are producing the same result:

=SUMPRODUCT((DataAnalysis!$C$1:$C$117=A2)*(DataAnalysis!$B$1:$B$117="NO"))
=SUMPRODUCT(--(DataAnalysis!$C$1:$C$117=A2),--(DataAnalysis!$B$1:$B$117="NO"))

Many Thanks

 

[VoG]

-- and * are different coercers that have the same effect. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mrexcel.com/forum/showpost.php?p=337954&postcount=8

 

[Aladin Akyurek]

Hello guys,

I was wondering if anyone can explain why the following formulas, are producing the same result:

=SUMPRODUCT((DataAnalysis!$C$1:$C$117=A2)*(DataAnalysis!$B$1:$B$117="NO"))
=SUMPRODUCT(--(DataAnalysis!$C$1:$C$117=A2),--(DataAnalysis!$B$1:$B$117="NO"))

Many Thanks

They are identical in behavior. The latter follows the native syntax of SumProduct, the so-called comma-syntax, while the former is using the * syntax, fashioned after the non-IF array formulas...

=SUM((DataAnalysis!$C$1:$C$117=A2)*(DataAnalysis!$B$1:$B$117="NO"))

which you need to confirm with control+shift+enter, not just enter.

For more on the syntax issue, see:

http://www.mrexcel.com/forum/showthread.php?t=70547 (post #8)