Sumproduct Search, Sum Durations

[mp80237]

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!!

 

[Pete_UK]

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

 

[Herbert Seidenberg]

Excel 2007 PivotTable, PivotChart
No formulas needed.
http://www.mediafire.com/file/zmzbryxmqzi/02_03_10.xlsx

 

[mp80237]

Thank you so much Pete, that worked!!

 

[mp80237]

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!!

 

[Pete_UK]

Glad to hear it - thanks for feeding back.

Pete