I should have specified that "substracting means AND NOT" is only valid if
the second condition CAN'T be TRUE when the first condition is FALSE.

--
Festina Lente


"PapaDos" wrote:

The equivalent of
=SUMPRODUCT(--(D2:D9100),--(F2:F9100),--(H2:H9100))
is
=SUMPRODUCT((D2:D9100) * (F2:F9100) * (H2:H9100))

In "array arithmetics" done on boolean values, multiplying means AND, adding
means OR, substracting means AND NOT.

So to get the equivalent of
=COUNTIF(D2:D9,"100")+COUNTIF(F2:F9,"100")+COUNT IF(H2:H9,"100")
is
=SUMPRODUCT((D2:D9100) + (F2:F9100) + (H2:H9100))

To get the counts between a range, use
=SUMPRODUCT((D2:D9100) - (D2:D9=120) + (F2:F9100) - (F2:F9=120) +
(H2:H9100) - (H2:H9=120))
or
=SUMPRODUCT((D2:D9100) * (D2:D9<120) + (F2:F9100) * (F2:F9<120) +
(H2:H9100) * (H2:H9<120))

--
Festina Lente


"Rex" wrote:

=COUNTIF(D2:D9,"100")+COUNTIF(F2:F9,"100")+COUNT IF(H2:H9,"100") answer 14
=COUNT(D2:D9,F2:F9,H2:H9) answer 18

=SUMPRODUCT(--(D2:D9100),--(F2:F9100),--(H2:H9100)) /
COUNT(D2:D9,F2:F9,H2:H9) answer 0.16667 should be 0.7777 (14/18)

Also, how do I count within a range <120 and 100 in the above formula?

Thanks in advance