Using COUNTIF with two different criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to count the number of rows that have SEVERITY of "critical" AND
STATUS of "open". I've used COUNTIF to count with only one condition. Any
ideas?

SEVERITY STATUS
critical open
critcal closed
 
If SEVERITY is in [A1:A100] and STATUS is in [B1:B100] then:

=SUMPRODUCT((A1:A100="critical")*(B1:B100="open"))

Regards,
KL
 
=sumproduct(--(a1:a10="critical"),--(b1:b10="open"))

=sumproduct() likes to work with numbers.
-- converts trues and falses to +1's and 0's.
 
Lucas Soler said:
I'd like to count the number of rows that have SEVERITY of "critical" AND
STATUS of "open". I've used COUNTIF to count with only one condition.
Any
ideas?

SEVERITY STATUS
critical open
critcal closed
Click to expand...

I do this by concatenating the elements in a 3rd column then using the value
of the concatenatoin as the criteria ie formula un column c is
=a2*b2
give the range a name like Concat
then = countif(concat,"criticalopen")
or to make it a little more dynamic, you can have row and column headings
where you have your summary data called (rows) critical, non-critical and
open, closed then use:
=countif(Concat, $A2&B$1)

that sort of thing.
 
Damien McBain said:
I do this by concatenating the elements in a 3rd column then using the
value of the concatenatoin as the criteria ie formula un column c is
=a2*b2
give the range a name like Concat
then = countif(concat,"criticalopen")
or to make it a little more dynamic, you can have row and column headings
where you have your summary data called (rows) critical, non-critical and
open, closed then use:
=countif(Concat, $A2&B$1)

that sort of thing.
Click to expand...

Oh, same prinicple for SUMIF as well
 
Back
Top