Summarizing Information on Select Tabs

[J G]

If you would open the attached doc, you will find that I am able to summarize similar information across all tabs which are identified as Project 1 - 4. Each project falls under a department, either Dept 10 or 20. I don't know how to use the department ID in the formula that I use to summarize each dept.

Any ideas?

Thanks,
JG

 

[Luke M]

Before we get too far, I would propose that you're going about this backwards. Much better to have all raw data in one place, and then split it out to summary sheets.
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm#Layout

Your current layout makes gathering data very difficult, and would require macros or volatile INDIRECT functions, at the very least.

 

[J G]

Each of these project sheets is a budget and users use these sheets to enter data. I do have another sheet which all data dumps into which I use to upload into the system. This summary sheet will be used to verify the upload is correct by comparing the summary sheet to reports generated by the system.

 

[J G]

Anyone else have any ideas?

Thanks very much.

 

[NARAYANK991]

Hi ,

I am sure someone will come up with a more elegant formula , but see if this is acceptable.

Narayan

 

[J G]

Wow that's amazing!
I knew I could use the same basic formula used in the by month summary tab but I couldn't figure out where to put that last reference. I would never have guess an empty cell.

Thanks so much!

 

[Haseeb A]

Hello JG,

..or you can use this formula for Dept. summary without helper,

=SUMPRODUCT(SUMIF(INDIRECT("'"&ProjectList&"'!A:A"),$A2,OFFSET(INDIRECT("'"&ProjectList&"'!B:B"),,{0,1,2,3,4,5,6,7,8,9,10,11}))*(T(INDIRECT("'"&ProjectList&"'!B2"))=B$1))

Assuming B2 will be always text, if not you can replace last part with COUNTIF,

=SUMPRODUCT(SUMIF(INDIRECT("'"&ProjectList&"'!A:A"),$A2,OFFSET(INDIRECT("'"&ProjectList&"'!B:B"),,{0,1,2,3,4,5,6,7,8,9,10,11}))*COUNTIF(INDIRECT("'"&ProjectList&"'!B2"),B$1))