Sum the total of a column value that exist in multiple tabs

[dhroark]

I have a workbook that contains several different tabs. The first tab is a summary of the values that exist in the other tabs. I have a column value that may exist in one or more of the other tabs that I need to sum the totals of 3 of its columns. In the attached spreadsheet, I will use cell A24 (10547) in the summary tab as an example. I need to calculate the following values for Sensor 10547 through all tabs (FL, NY, OK, PA, SC) Total Miles Flown, Rejected Miles and Smearing Miles. The current values have been manually calculated.Excel_Forum_Reflight_Analysis.xlsx

[nigelbloomy]

This formula worked for total miles flown in cell C18. You could adjust it to find rejected miles and smearing miles.

=SUMPRODUCT((FL!$H$4:$H$35)*(FL!$B$4:$B$35=Summary!$A18))+
SUMPRODUCT((NY!$H$4:$H$154)*(NY!$B$4:$B$154=Summary!$A18))+
SUMPRODUCT((OK!$H$4:$H$99)*(OK!$B$4:$B$99=Summary!$A18))+
SUMPRODUCT((PA!$H$4:$H$68)*(PA!$B$4:$B$68=Summary!$A18))+
SUMPRODUCT((SC!$H$4:$H$187)*(SC!$B$4:$B$187=Summary!$A18))

[FDibbins]

a different approach...
1. make a list of all your sheet names, then give that list a range name - I used States.
2. Use this in C18, copied down...
=SUMPRODUCT(SUMIF(INDIRECT("'"&States&"'!B4:B200"),A18,INDIRECT("'"&States&"'!H4:H200")))
Hor the other totals, adjust the H4:H200 range accordingly

[dhroark]

Perfect! Thanks so much for the help.

[oeldere]

add the totals (in the sheets) per column in row 1, instead of below the table.

Then you only have to refer (in the master sheet) to the values in row 1 (in the other sheets).

[newdoverman]

I suggest a completely different approach to the data handling. Eliminate all the State worksheets and have all the data in a single table. Then for the summaries produce two pivot tables. No formulae are required and the updating of the summary is simply done by right clicking in each pivot table and refresh.

The proposal is located on the green tabs.

[oeldere]

Great job, newdoverman

[newdoverman]

Thanks, it is a case of "Let Excel do the work".

[newdoverman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.