software

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 quot;controlquot; 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=quot;controlquot;)*(B1:B5gt;=0.3)*(B1:B5lt; =0.4))

Jerry

quot;multiple conditions formulaquot; wrote:

gt; Hi, I've tried using COUNT, SUM, SUMIF, COUNTIF, SUMPRODUCT, and nesting IF
gt; functions, but I can't seem to get this formula to work.
gt; control 0.122
gt; GlcNAc 0.255
gt; GlcNAc 0.478
gt; control 0.377
gt; control 0.301
gt;
gt; I want to count the number of values for quot;controlquot; between 0.3 and 0.4, and
gt; have this count represented in a cell on a separate worksheet. Thanks a lot
gt; 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.

quot;Jerry W. Lewisquot; wrote:

gt; =SUMPRODUCT((A1:A5=quot;controlquot;)*(B1:B5gt;=0.3)*(B1:B5lt; =0.4))
gt;
gt; Jerry
gt;
gt; quot;multiple conditions formulaquot; wrote:
gt;
gt; gt; Hi, I've tried using COUNT, SUM, SUMIF, COUNTIF, SUMPRODUCT, and nesting IF
gt; gt; functions, but I can't seem to get this formula to work.
gt; gt; control 0.122
gt; gt; GlcNAc 0.255
gt; gt; GlcNAc 0.478
gt; gt; control 0.377
gt; gt; control 0.301
gt; gt;
gt; gt; I want to count the number of values for quot;controlquot; between 0.3 and 0.4, and
gt; gt; have this count represented in a cell on a separate worksheet. Thanks a lot
gt; gt; for all your help.

This is an undocumented, but quite common use of SUMPRODUCT

(A1:A5=quot;controlquot;) etc. each return an array of 5 booleans (True or False).

quot;*quot; is used instead of quot;,quot; to multiply the arrays together because quot;*quot; in
the process converts True-gt;1 and False-gt;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, quot;*quot; functions like quot;ANDquot; and quot; quot; functions like quot;ORquot;;
you can use them together with parentheses (to control order of evaluation)
to produce quite complicated conditions.

Jerry

quot;multiple conditions formulaquot; wrote:

gt; Thanks Jerry for the quick reply. It's giving me the right answer now,
gt; though it's not really a sum of the products of those values, is it? I mean,
gt; what's written looks like it should give the sum of 0.377 and 0.301 (0.678),
gt; and not the value 2, if I understood the help on that function in Excel,
gt; which I guess I didn't.
gt;
gt; quot;Jerry W. Lewisquot; wrote:
gt;
gt; gt; =SUMPRODUCT((A1:A5=quot;controlquot;)*(B1:B5gt;=0.3)*(B1:B5lt; =0.4))
gt; gt;
gt; gt; Jerry
gt; gt;
gt; gt; quot;multiple conditions formulaquot; wrote:
gt; gt;
gt; gt; gt; Hi, I've tried using COUNT, SUM, SUMIF, COUNTIF, SUMPRODUCT, and nesting IF
gt; gt; gt; functions, but I can't seem to get this formula to work.
gt; gt; gt; control 0.122
gt; gt; gt; GlcNAc 0.255
gt; gt; gt; GlcNAc 0.478
gt; gt; gt; control 0.377
gt; gt; gt; control 0.301
gt; gt; gt;
gt; gt; gt; I want to count the number of values for quot;controlquot; between 0.3 and 0.4, and
gt; gt; gt; have this count represented in a cell on a separate worksheet. Thanks a lot
gt; gt; gt; for all your help.