| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM with multiple numeric and text criteria
Hello,
I would like to be able to return a variety of metrics for a range of cells that text and numbers. In the first example, I would like to be able to return the total number of records where the value €śDev_Assigned€ť is in Column B, the value €ś1-Critical€ť is in Column C and the number in Column D is greater than one but less than 4: Column A Column B Column C Column D Column E WUP00020460 Dev_Assigned 1-Critical 2 CW App Issue WUP00020596 Dev_Assigned 1-Critical 2 CW Env Issue WUP00020731 Test 1-Critical 1 CW Env Issue WUP00020465 Dev_Assigned 2-High 2 CW Env Issue WUP00020461 Test 3-Medium 2 CW Data Issue WUP00020592 Open 2-High 2 CW User Issue WUP00020733 Dev_Assigned 1-Critical 4 CW Data Issue WUP00020464 Test 2-High 2 CW Data Issue WUP00020465 Dev_Assigned 1-Critical 5 CW App Issue WUP00020597 Dev_Assigned 1-Critical 3 CW Env Issue WUP00020738 Test 1-Critical 1 CW Test Issue WUP00020469 Dev_Assigned 2-High 2 CW Env Issue In the second example, using the data above, I would like to be able to return the total number of records where the value €ś1-Critical€ť is in Column C and the value €śCW App Issue€ť is in Column E. Any help or guidance is greatly appreciated! -- Thanks! -mcdc01 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM with multiple numeric and text criteria
One way:
=SUMPRODUCT(--(C1:C1000="1-Critical"),--(E1:E1000="CW App Issue")) for an explanation of the "--", see http://www.mcgimpsey.com/excel/doubleneg.html In article , mcdc01 wrote: Hello, I would like to be able to return a variety of metrics for a range of cells that text and numbers. In the first example, I would like to be able to return the total number of records where the value €śDev_Assigned€ť is in Column B, the value €ś1-Critical€ť is in Column C and the number in Column D is greater than one but less than 4: Column A Column B Column C Column D Column E WUP00020460 Dev_Assigned 1-Critical 2 CW App Issue WUP00020596 Dev_Assigned 1-Critical 2 CW Env Issue WUP00020731 Test 1-Critical 1 CW Env Issue WUP00020465 Dev_Assigned 2-High 2 CW Env Issue WUP00020461 Test 3-Medium 2 CW Data Issue WUP00020592 Open 2-High 2 CW User Issue WUP00020733 Dev_Assigned 1-Critical 4 CW Data Issue WUP00020464 Test 2-High 2 CW Data Issue WUP00020465 Dev_Assigned 1-Critical 5 CW App Issue WUP00020597 Dev_Assigned 1-Critical 3 CW Env Issue WUP00020738 Test 1-Critical 1 CW Test Issue WUP00020469 Dev_Assigned 2-High 2 CW Env Issue In the second example, using the data above, I would like to be able to return the total number of records where the value €ś1-Critical€ť is in Column C and the value €śCW App Issue€ť is in Column E. Any help or guidance is greatly appreciated! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM with multiple numeric and text criteria
That is perfect for the second example, Thank you!
For the first example, would the formula be: =SUMPRODUCT(--(B1:B1000="Dev_Assigned"),--(C1:C1000="1-Critical"),--(D1:D1000="2,3€ť)) Thanks again! -- Thanks! -mcdc01 "JE McGimpsey" wrote: One way: =SUMPRODUCT(--(C1:C1000="1-Critical"),--(E1:E1000="CW App Issue")) for an explanation of the "--", see http://www.mcgimpsey.com/excel/doubleneg.html In article , mcdc01 wrote: Hello, I would like to be able to return a variety of metrics for a range of cells that text and numbers. In the first example, I would like to be able to return the total number of records where the value €œDev_Assigned€ is in Column B, the value €œ1-Critical€ is in Column C and the number in Column D is greater than one but less than 4: Column A Column B Column C Column D Column E WUP00020460 Dev_Assigned 1-Critical 2 CW App Issue WUP00020596 Dev_Assigned 1-Critical 2 CW Env Issue WUP00020731 Test 1-Critical 1 CW Env Issue WUP00020465 Dev_Assigned 2-High 2 CW Env Issue WUP00020461 Test 3-Medium 2 CW Data Issue WUP00020592 Open 2-High 2 CW User Issue WUP00020733 Dev_Assigned 1-Critical 4 CW Data Issue WUP00020464 Test 2-High 2 CW Data Issue WUP00020465 Dev_Assigned 1-Critical 5 CW App Issue WUP00020597 Dev_Assigned 1-Critical 3 CW Env Issue WUP00020738 Test 1-Critical 1 CW Test Issue WUP00020469 Dev_Assigned 2-High 2 CW Env Issue In the second example, using the data above, I would like to be able to return the total number of records where the value €œ1-Critical€ is in Column C and the value €œCW App Issue€ is in Column E. Any help or guidance is greatly appreciated! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
| Numeric in Text to convert back to the form of Numeric for VLookup Purposes | Excel Discussion (Misc queries) | |||
| multiple numeric values applied to single text | Excel Discussion (Misc queries) | |||
| Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
| Formulas dealing with text data | Excel Worksheet Functions | |||
Linear Mode
