Tuesday, February 22, 2011

yogi_SumAmountsForCategoriesFromDifferent Sheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

cstheday52 said:
I have a household budget workbook. It has 14 tabs, or separate sheets. I would like to Sum expenditures that have the same label, but are on a number of different sheets. Expenditures are in Column C (Amount) the label is in Column E (Location).
For this illustration, I used the data from only 3 sheets, but the solution can be used for any number of sheets. I used the VMERGE script by ahab to vertically merge the data in one thable and then I used the QUERY function to group the Amounts by Categories. The formula used in the Master sheet cell B1 is ...

=query(vmerge(Sheet1!C$1:E100,Sheet2!C$2:E100,Sheet3!C$2:E100),"select Col3,sum(Col1) where Col1 is not null group by Col3 label sum(Col1) 'Amount' ",1)