Counts for Excel 2003 using multiple criteria
 

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!

Counts for Excel 2003 using multiple criteria
 

Yes, It's possible

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


" wrote:

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!

Counts for Excel 2003 using multiple criteria
 

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


"Teethless mama" wrote in message
...
Yes, It's possible

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


" wrote:

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!

Counts for Excel 2003 using multiple criteria
 

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.


"Teethless mama" wrote:

Yes, It's possible

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


" wrote:

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!

Counts for Excel 2003 using multiple criteria
 

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.



"Susan" wrote:

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.


"Teethless mama" wrote:

Yes, It's possible

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


" wrote:

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!

Counts for Excel 2003 using multiple criteria
 

Nevermind. I figured it out. Thanks everyone!

"Susan" wrote:

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.



"Susan" wrote:

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.


"Teethless mama" wrote:

Yes, It's possible

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


" wrote:

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!

Counts for Excel 2003 using multiple criteria
 

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


--


Regards,


Peo Sjoblom



"Susan" wrote in message
...
Nevermind. I figured it out. Thanks everyone!

"Susan" wrote:

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.



"Susan" wrote:

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.


"Teethless mama" wrote:

Yes, It's possible

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


" wrote:

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!