Counts for Excel 2003 using multiple criteria

[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!

 

[Guest]

Yes, It's possible

=SUMPRODUCT(-(Status="Open"),-(Serverity<>"Minor"))

 

[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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

Nevermind. I figured it out. Thanks everyone!

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.

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom