sumproduct

[Guest]

=COUNTIF(D29,">100")+COUNTIF(F2:F9,">100")+COUNTIF(H2:H9,">100") answer 14
=COUNT(D29,F2:F9,H2:H9) answer 18

=SUMPRODUCT(--(D29>100),--(F2:F9>100),--(H2:H9>100)) /
COUNT(D29,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

 

[Tom Ogilvy]

The Sumproduct formula requires that all three columns have values greater
than 100 to be counted. The 3 countif formuls do not have this
restricition. The count the total cells >100 regardless of the value of
other cells in the row. The max value for the numerator (the sumproduct)
will be the number of rows.

=SUMPRODUCT(--(D29>100),--(F2:F9>100),--(H2:H9>100),--(D29<120),--(F2:F9<120),--(H2:H9<120))
/ COUNT(D29,F2:F9,H2:H9)

 

[Guest]

The equivalent of
=SUMPRODUCT(--(D29>100),--(F2:F9>100),--(H2:H9>100))
is
=SUMPRODUCT((D29>100) * (F2:F9>100) * (H2:H9>100))

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

So to get the equivalent of
=COUNTIF(D29,">100")+COUNTIF(F2:F9,">100")+COUNTIF(H2:H9,">100")
is
=SUMPRODUCT((D29>100) + (F2:F9>100) + (H2:H9>100))

To get the counts between a range, use
=SUMPRODUCT((D29>100) - (D29>=120) + (F2:F9>100) - (F2:F9>=120) +
(H2:H9>100) - (H2:H9>=120))
or
=SUMPRODUCT((D29>100) * (D29<120) + (F2:F9>100) * (F2:F9<120) +
(H2:H9>100) * (H2:H9<120))

 

[Guest]

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.

 

[Dallman Ross]

In <[email protected]>, Tom Ogilvy

The Sumproduct formula requires that all three columns have
values greater than 100 to be counted. The 3 countif formuls
do not have this restricition. The count the total cells >100
regardless of the value of other cells in the row. The max value
for the numerator (the sumproduct) will be the number of rows.

=SUMPRODUCT(--(D29>100),--(F2:F9>100),--(H2:H9>100),--(D29<120),--(F2:F9<120),--(H2:H9<120))
/ COUNT(D29,F2:F9,H2:H9)

Good explanation. Thank you, Tom! I have a further question.
I recently added some not dissimilar SUMPRODUCT formula columns
to fairly large tables I use daily. The workbook has become difficult
to use, because it recalculates constantly now. I do understand
why and have read up on it, and am thinking through my options about
how to change my setup to get rid of the delays. My question here
is, would the COUNTIF style help me in re. calculation "effort"
and efficiency and possibly help me out of my problem?

Thanks for any ideas.

Dallman Ross

 

[Guest]

=SUMPRODUCT((D29>140) + (F2:F9>140) + (H2:H9>140)) /
COUNT(D29,F2:F9,H2:H9) this works with the one variable >140

=SUMPRODUCT((D29>140) * (D29<160) + (F2:F9>140) * (F2:F9<160) +
(H2:H9>140) * (H2:H9<160)) /COUNT(D29,F2:F9,H2:H9) this works with two
variables >140 and <160 Thanks for your help.