COUNTIF?

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

I'm trying to use the COUNTIF function to count the
occurences of certain loan codes in a spreadsheet,
however, I need the formula to meet more than one
criteria. For example, I need to count how many times the
code "EM" appears in column C, but only if the
corresponding value in column H is greater than zero.
Please help. Thanks.
 
You can use sumproduct, basically;

Sumproduct((C1:C999="EM")*(H1:H999>0))

that assumes EM would be the only text in column C.
 
Hi Brad

COUNTIF does not support multiple criteria, however you can use a
combination of SUM and IF. Try the following formula (assuming that you
have 8 rows):
=SUM(IF((C1:C8="EM")*(H1:H8<>0);1;0))
an enter it as an array formula (CRTL-SHIFT-RETURN)

Frank
 
Dave R. said:
You can use sumproduct, basically;

Sumproduct((C1:C999="EM")*(H1:H999>0))

that assumes EM would be the only text in column C.
Click to expand...

That is not correct, it will count the number of instances of EM in column
C, regardless of what is there, and , returning an array of True/False
values, and building a corresponding array of True/False for the H1:H999,
and multiplies all of these together (True*True=1, anything else =0). This
is how it counts the 2 (or more) conditions.
 
Hi Bob, I am aware of how it works, but thanks for the lesson.

As I understand the OPs question, he wants to count the number of row of EM
codes when the corresponding H is greater than 0. That is precisely what
that formula does. Am I mis-interpreting his question?

Have you tried it? Why do you think it counts all occurences of EM in C
"regardless of what is there" (and what do you mean by that?) ? Of course it
looks in H, thats what the second part of the formula is.
 
Bob, I see where the confusion lies. The meaning of "that assumes EM would
be the only text in column C." is: the formula is written to look only for
"EM" and not "2EM" or "EM1".

There were assumptions about: his knowledge of his data, his knowledge of
Excel (he tried various other formulas) and him reading the formula, that
make the sentence work.
 
Brad,
Dave's comment about "not correct" refers to the assumption that EM is the only text in column H. Dave's formula will work fine even if other text is in the column.

Also, since there's more then one way to skin a cat, you can also use.

=SUMPRODUCT(--(C1:C999="EM"),--(H1:H999>0))

Good Luck,
Mark Graesser
(e-mail address removed)

----- Bob Phillips wrote: -----



Dave R. said:
You can use sumproduct, basically;
Click to expand...

That is not correct, it will count the number of instances of EM in column
C, regardless of what is there, and , returning an array of True/False
values, and building a corresponding array of True/False for the H1:H999,
and multiplies all of these together (True*True=1, anything else =0). This
is how it counts the 2 (or more) conditions.
 
Dave,

That is how I read it, that you couldn't even have "2EM" or whatever, and I
see what you mean now.

You have to be so careful with phrasing don't you<vbg>.

Regards

Bob
 
Mark Graesser said:
Also, since there's more then one way to skin a cat, you can also use.

=SUMPRODUCT(--(C1:C999="EM"),--(H1:H999>0))
Click to expand...

That is exactly the same as Dave R's formula, except that you coerce the
True/False array in to 1/0s immediately with the '--' before each test,
whereas Dave's does that coercion by multiplying two True/False arrays,
which Excel resolves to 1/0. This habit of using '--' has sprung up,
recently, unnecessarily in my view as you introduce 4 operators instead of 1
and make it less clear. I understand that it is necessary if you only have 1
condition, but now when you can resolve it with 2 conditions.

A true alternative is that posted by Frank, namely

=SUM(IF((C1:C8="EM")*(H1:H8<>0);1;0))
an enter it as an array formula (CRTL-SHIFT-RETURN)


Bob
 
I think I've read that it was faster to do it that way. I don't know if
someone has done tests or not.
 
Back
Top