| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple Tab Counts | Excel Discussion (Misc queries) | |||
| Pulling Multiple Totals, Averages, and Counts | Excel Discussion (Misc queries) | |||
| Need formula that Counts items matching criteria using two columns | Excel Worksheet Functions | |||
| Counts/Percents Multiple Criteria | Excel Worksheet Functions | |||
| Select by multiple criteria (Excel 2003) | Excel Worksheet Functions | |||
Hybrid Mode
