Counts for Excel 2003 using multiple criteria

S

susanohkwon

I have a two part question.

Part 1 - I need to get the count based on the criteria in two columns,
In the below
example I am looking for a formula that will give me the count for
defects
that are having the status of OPEN and where there is ANY VALUE in the
severity column.

Defect# Status Severity
1 Open Critical
2 Closed
3 Open Important
4 Closed
5 Open Minor
6 Open Critical
7 Closed
8 Open Important
9 Closed Critical
10 Open Minor

Part 2 - I would like to know how to create a count based on multiple
criteria where a value is such and the second value is "not ____". For
example, a count where Status is open and severity is NOT Minor. Is
this possible using Excel 2003?

Thanks in advance!
 
P

Peo Sjoblom

I don't see the point in using a single minus, what if the OP wants to
expand it and the ranges are uneven in numbers?

=SUMPRODUCT(-(Status="Open"),-(Severity<>"Minor"),-(Range="x"))

that will give a negative count


--


Regards,


Peo Sjoblom
 
S

Susan

Thank you very much for your response. What about searching for any value?

ie Count defects that are having the status of OPEN and where there is ANY
VALUE in the severity column.

Thanks again.
 
S

Susan

Ultimately, I would like to combine this formula to iinclude three criteria

=SUMPRODUCT(-(Status="Open"),-(Serverity<>"Minor")) and search a third
column where column has any value, ie not null.
 
S

Susan

Nevermind. I figured it out. Thanks everyone!

Susan said:
Ultimately, I would like to combine this formula to iinclude three criteria

=SUMPRODUCT(-(Status="Open"),-(Serverity<>"Minor")) and search a third
column where column has any value, ie not null.
 
P

Peo Sjoblom

Use the double minuses -- instead of - if you have 3 ranges


--


Regards,


Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top