Three same Formulas

[John]

Hi everyone

I've got these three sumproduct formulas that returns the right answer.
=SUMPRODUCT((YEAR(A5:A150)=2008)*(C5:C150="Dept021")*B5:B150)
=SUMPRODUCT(--(YEAR(A5:A150)=2008),--(C5:C150="Dept021"),B5:B150)
=SUMPRODUCT(N(YEAR(A5:A150)=2008),N(C5:C150="Dept021"),B5:B150)
I would like to know if they're all the same.
Can they be use for different situation.
Any preference over others and for what reason?

Thank you
Regards
John

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Spiky]

Hi everyone

I've got these three sumproduct formulas that returns the right answer.
=SUMPRODUCT((YEAR(A5:A150)=2008)*(C5:C150="Dept021")*B5:B150)
=SUMPRODUCT(--(YEAR(A5:A150)=2008),--(C5:C150="Dept021"),B5:B150)
=SUMPRODUCT(N(YEAR(A5:A150)=2008),N(C5:C150="Dept021"),B5:B150)
I would like to know if they're all the same.
Can they be use for different situation.
Any preference over others and for what reason?

Thank you
Regards
John

They are all the same. You need to use an operator to get SUMPRODUCT
to see your logical expressions with 1s and 0s instead of TRUEs and
FALSEs. Those formulas all do that in a different way. The most common
seems to be the "--", probably because it is distinctive and
versatile. Pick which you like and always use it so it will be
familiar.