sumproduct

[Guest]

Why is:


=SUMPRODUCT(--(F1:F5={2,3,4,5}))

equivalent to the number of times 2,3,4, or 5 occur within the range??

thanks,

flkulchar

 

[Aladin Akyurek]

In G1 enter & copy down:

=OR(F1=2,F1=3,F1=4,F1=5)

In H1 enter & copy down:

=--G1

or

=G1+0

which are equivalent qua effect.

Now total H1:H5 with:

=SUM(H1:H5)

Why is:


=SUMPRODUCT(--(F1:F5={2,3,4,5}))

equivalent to the number of times 2,3,4, or 5 occur within the range??

thanks,

flkulchar

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.