Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Search, Sum Durations
Hello, I have a table that I need to get information out of using Excel.
Below is a sample from the detail data. systemslist severity Duration Minutes Fare System - NG 3 25 Fares System 3 25 Fare System - NG 2 15 Fares System 1 5 System Issues - PM 3 35 System Issues 2 10 Air System, Production System, System Issues 0 4 System Issues 0 3 System Issues - PM 0 3 System Issues 3 10 System Issues 3 20 Air System, Production System, System Issues 1 29 System Issues 1 29 System Issues 1 29 System Issues 3 38 I have a summary page. I am getting correct numbers on the Count section, but the duration section the numbers are off Count Total Sev0 Sev1 Sev2 Sev3 Fares System 2 0 1 1 2 System Issues 11 3 3 1 4 Air System 2 1 1 0 0 Duration Total Sev0 Sev1 Sev2 Sev3 Fares System 2 0 4 4 8 System Issues 11 9 9 3 12 Air System 2 3 3 0 0 The formula for the count for the Fare System, Sev 3 is =SUMPRODUCT(--(ISNUMBER(SEARCH("*Fare*",Sheet1!$A$1:$A$70)+SEARC H("3",Sheet1!$B$1:$B$70)))) That is working great. The duration for Fare System, Sev 3 is =SUMPRODUCT(--(ISNUMBER(SEARCH("*Fare*",Sheet1!$A$1:$A$70)+SEARC H("3",Sheet1!$B$1:$B$70))))*(Sheet1!$C$2:$C$70). That is not working right at all. I was thinking of putting a coma after the first search, but it is saying there are too many arguments for this function. Please please help. Thank you so much!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Search, Sum Durations
The ranges should be the same size. Also, the range you want to add
should be part of the SP function, but you have it outside. Try it this way: =SUMPRODUCT(--(ISNUMBER(SEARCH("*Fare*",Sheet1!$A$1:$A$70)+ SEARCH("3",Sheet*1!$B$1:$B$70))),Sheet1!$C$1:$C$70 ) Hope this helps. Pete On Feb 3, 6:27*pm, mp80237 wrote: Hello, I have a table that I need to get information out of using Excel. * Below is a sample from the detail data. systemslist * * severity * * * *Duration Minutes Fare System *- NG * * * 3 * * * 25 Fares System * *3 * * * 25 Fare System *- NG * * * 2 * * * 15 Fares System * *1 * * * 5 System Issues - PM * * *3 * * * 35 System Issues * 2 * * * 10 Air System, Production System, System Issues * *0 * * * 4 System Issues * 0 * * * 3 System Issues - PM * * *0 * * * 3 System Issues * 3 * * * 10 System Issues * 3 * * * 20 Air System, Production System, System Issues * *1 * * * 29 System Issues * 1 * * * 29 System Issues * 1 * * * 29 System Issues * 3 * * * 38 I have a summary page. *I am getting correct numbers on the Count section, but the duration section the numbers are off Count * Total * Sev0 * *Sev1 * *Sev2 * *Sev3 Fares System * *2 * * * 0 * * * 1 * * * 1 * * * 2 System Issues * 11 * * *3 * * * 3 * * * 1 * * * 4 Air System * * *2 * * * 1 * * * 1 * * * 0 * * * 0 Duration * * * *Total * Sev0 * *Sev1 * *Sev2 * *Sev3 Fares System * *2 * * * 0 * * * 4 * * * 4 * * * 8 System Issues * 11 * * *9 * * * 9 * * * 3 * * * 12 Air System * * *2 * * * 3 * * * 3 * * * 0 * * * 0 The formula for the count for the Fare System, Sev 3 is =SUMPRODUCT(--(ISNUMBER(SEARCH("*Fare*",Sheet1!$A$1:$A$70)+SEARC H("3",Sheet*1!$B$1:$B$70)))) That is working great. The duration for Fare System, Sev 3 is =SUMPRODUCT(--(ISNUMBER(SEARCH("*Fare*",Sheet1!$A$1:$A$70)+SEARC H("3",Sheet*1!$B$1:$B$70))))*(Sheet1!$C$2:$C$70) . *That is not working right at all. * I was thinking of putting a coma after the first search, but it is saying there are too many arguments for this function. Please please help. *Thank you so much!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Search, Sum Durations
Excel 2007 PivotTable, PivotChart
No formulas needed. http://www.mediafire.com/file/zmzbryxmqzi/02_03_10.xlsx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Search, Sum Durations
Thank you so much Pete, that worked!!
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Search, Sum Durations
The pivot table wouldn't have worked with this. I have actually over 700
rows of data where many of the systemlist have combined systems. To go thru and manually break them out would have taken days. But thank you so much for the help!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Search, Sum Durations
Glad to hear it - thanks for feeding back.
Pete On Feb 4, 3:20*pm, mp80237 wrote: Thank you so much Pete, that worked!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SEARCH IN SUMPRODUCT | Excel Discussion (Misc queries) | |||
SUMPRODUCT containing txt string search | Excel Worksheet Functions | |||
durations | Excel Discussion (Misc queries) | |||
Search function needed (sumproduct?) | Excel Worksheet Functions | |||
SUMPRODUCT and search string | Excel Worksheet Functions |