Sumproduct Search, Sum Durations
in [Functions]
|
From: mp80237 on 3 Feb 2010 13:27 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)+SEARCH("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)+SEARCH("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!!
From: Pete_UK on 3 Feb 2010 19:58 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",Sheet1!$B$1:$B$70))),Sheet1!$C$1:$C$70) Hope this helps. Pete On Feb 3, 6:27 pm, mp80237 <mp80...(a)discussions.microsoft.com> 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)+SEARCH("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)+SEARCH("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!!
From: Herbert Seidenberg on 3 Feb 2010 20:20 Excel 2007 PivotTable, PivotChart No formulas needed. http://www.mediafire.com/file/zmzbryxmqzi/02_03_10.xlsx
From: mp80237 on 4 Feb 2010 10:20 Thank you so much Pete, that worked!!
From: mp80237 on 4 Feb 2010 10:22
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!! |