Hi, I've tried using COUNT, SUM, SUMIF, COUNTIF, SUMPRODUCT, and nesting IF
functions, but I can't seem to get this formula to work.
control 0.122
GlcNAc 0.255
GlcNAc 0.478
control 0.377
control 0.301

I want to count the number of values for "control" between 0.3 and 0.4, and
have this count represented in a cell on a separate worksheet. Thanks a lot
for all your help.

=SUMPRODUCT((A1:A5="control")*(B1:B5=0.3)*(B1:B5< =0.4))

Jerry

"multiple conditions formula" wrote:

Hi, I've tried using COUNT, SUM, SUMIF, COUNTIF, SUMPRODUCT, and nesting IF
functions, but I can't seem to get this formula to work.
control 0.122
GlcNAc 0.255
GlcNAc 0.478
control 0.377
control 0.301

I want to count the number of values for "control" between 0.3 and 0.4, and
have this count represented in a cell on a separate worksheet. Thanks a lot
for all your help.

Thanks Jerry for the quick reply. It's giving me the right answer now,
though it's not really a sum of the products of those values, is it? I mean,
what's written looks like it should give the sum of 0.377 and 0.301 (0.678),
and not the value 2, if I understood the help on that function in Excel,
which I guess I didn't.

"Jerry W. Lewis" wrote:

=SUMPRODUCT((A1:A5="control")*(B1:B5=0.3)*(B1:B5< =0.4))

Jerry

"multiple conditions formula" wrote:

Hi, I've tried using COUNT, SUM, SUMIF, COUNTIF, SUMPRODUCT, and nesting IF
functions, but I can't seem to get this formula to work.
control 0.122
GlcNAc 0.255
GlcNAc 0.478
control 0.377
control 0.301

I want to count the number of values for "control" between 0.3 and 0.4, and
have this count represented in a cell on a separate worksheet. Thanks a lot
for all your help.

This is an undocumented, but quite common use of SUMPRODUCT

(A1:A5="control") etc. each return an array of 5 booleans (True or False).

"*" is used instead of "," to multiply the arrays together because "*" in
the process converts True-1 and False-0, so you end up with an array of 5
ones (if all 3 conditions are satisfied) or zeros (otherwise). Summing that
array or zeros and ones is equivalent to counting the number of cases where
all 3 conditions are satisfied.

SUMPRODUCT was used instead of SUM because SUM would need to have been array
entered.

In boolean arithmetic, "*" functions like "AND" and "+" functions like "OR";
you can use them together with parentheses (to control order of evaluation)
to produce quite complicated conditions.

Jerry

"multiple conditions formula" wrote:

Thanks Jerry for the quick reply. It's giving me the right answer now,
though it's not really a sum of the products of those values, is it? I mean,
what's written looks like it should give the sum of 0.377 and 0.301 (0.678),
and not the value 2, if I understood the help on that function in Excel,
which I guess I didn't.

"Jerry W. Lewis" wrote:

=SUMPRODUCT((A1:A5="control")*(B1:B5=0.3)*(B1:B5< =0.4))

Jerry

"multiple conditions formula" wrote:

Hi, I've tried using COUNT, SUM, SUMIF, COUNTIF, SUMPRODUCT, and nesting IF
functions, but I can't seem to get this formula to work.
control 0.122
GlcNAc 0.255
GlcNAc 0.478
control 0.377
control 0.301

I want to count the number of values for "control" between 0.3 and 0.4, and
have this count represented in a cell on a separate worksheet. Thanks a lot
for all your help.