Using COUNTIF with two different criteria

[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

 

[Dick Kusleika]

Lucas

You can use and array formula for that. Also, the SUMPRODUCT worksheet
function does the same work as an array formula. Array formulas are entered
and editied with Control+Shift+Enter, not just enter. Yours may look like

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

For more on array formulas, see
http://www.dicks-blog.com/archives/2004/04/05/anatomy-of-an-array-formula/
http://www.dicks-blog.com/archives/2004/05/06/counting-with-array-formulas/

 

[KL]

If SEVERITY is in [A1:A100] and STATUS is in [B1:B100] then:

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

Regards,
KL

 

[Dave Peterson]

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

=sumproduct() likes to work with numbers.
-- converts trues and falses to +1's and 0's.

 

[KL]

Hi Dick,

I guess -- is redundant here as * does the coersion job.

Regards,
KL

 

[Damien McBain]

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

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]

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.

Oh, same prinicple for SUMIF as well

 

[Dick Kusleika]

KL

Right. It's a habit when I'm counting with arrays instead of summing.