Using COUNTIF with two different criteria
 

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

Using COUNTIF with two different criteria
 

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/2...array-formula/
http://www.dicks-blog.com/archives/2...rray-formulas/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Lucas Soler wrote:
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

Using COUNTIF with two different criteria
 

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

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

Regards,
KL


"Lucas Soler" wrote in message
...
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

Using COUNTIF with two different criteria
 

=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 wrote:

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

--

Dave Peterson

Using COUNTIF with two different criteria
 

Hi Dick,

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

Regards,
KL

"Dick Kusleika" wrote in message
...
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/2...array-formula/
http://www.dicks-blog.com/archives/2...rray-formulas/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Lucas Soler wrote:
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

Using COUNTIF with two different criteria
 

"Lucas Soler" wrote in message
...
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.

Using COUNTIF with two different criteria
 

"Damien McBain" wrote in message
...
"Lucas Soler" wrote in message
...
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.

Oh, same prinicple for SUMIF as well

Using COUNTIF with two different criteria
 

KL

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

KL wrote:
Hi Dick,

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

Regards,
KL

"Dick Kusleika" wrote in message
...
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/2...array-formula/
http://www.dicks-blog.com/archives/2...rray-formulas/

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Lucas Soler wrote:
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