Sumproduct

[Dave]

Hi,

Consider the following examples of the same Sumproduct formula all similarly
constructed:-

=SUMPRODUCT(N($A$1:$A$20="This")*N($B$1:$B$20="That"))
=SUMPRODUCT(($A$1:$A$20="This")^1*($B$1:$B$20="That")^1)
=SUMPRODUCT(($A$1:$A$20="This")+0*($B$1:$B$20="That")+0)
=SUMPRODUCT(($A$1:$A$20="This")*1*($B$1:$B$20="That")*1)
=SUMPRODUCT(($A$1:$A$20="This")*($B$1:$B$20="That"))

All the above work perfectly well and I understand that the first 4 coerce
the True/False evaluation to 1 & 0.
What I don't understand is when would each be selected in preference to the
other and why bother at all when the 5th example works perfectly well.

In my simple (and probably incorrectly advised) world the only time I would
resort to one of the first 4 would be for a formula like:-
=SUMPRODUCT(--($A$1:$A$20="This"))
Because
=SUMPRODUCT(($A$1:$A$20="This"))
would fail but having selected the double unary (as most seem to do) why not
the N switch or ^1 for example.

D

 

[Dave]

I forgot to include the double unary version

=SUMPRODUCT(--($A$1:$A$20="This")*--($B$1:$B$20="That"))

 

[David Biddulph]

The double unary minus version would usually be
=SUMPRODUCT(--($A$1:$A$20="This"),--($B$1:$B$20="That"))
using the comma instead of the multiplication sign, because (as you've
pointed out) the multiplication already does the job of coercing to a
number.