Three same Formulas

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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
Click to expand...

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.
 
Back
Top