sumproduct
 

=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

sumproduct
 

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(--(D2:D9100),--(F2:F9100),--(H2:H9100),--(D2:D9<120),--(F2:F9<120),--(H2:H9<120))
/ COUNT(D2:D9,F2:F9,H2:H9)

--
Regards,
Tom Ogilvy



"Rex" wrote in message
...
=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

sumproduct
 

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

sumproduct
 

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

sumproduct
 

In , Tom Ogilvy
spake thusly in response to "Rex,"
who had written:

"Rex" wrote in message
...

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


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(--(D2:D9100),--(F2:F9100),--(H2:H9100),--(D2:D9<120),--(F2:F9<120),--(H2:H9<120))
/ COUNT(D2:D9,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

sumproduct
 

=SUMPRODUCT((D2:D9140) + (F2:F9140) + (H2:H9140)) /
COUNT(D2:D9,F2:F9,H2:H9) this works with the one variable 140

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



"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