Using SumIf from multiple tabs

[AnnaG87]

Hi there,

I'm trying to create a summary tab for the attached spreadsheet that adds up all of the columns B, C, D, from the three green tabs, but based on the subtotalled shaded cells.

Is this possible to pull from multiple tabs? If not, does anyone have any suggestions as to how to complete this?

Thanks for the help. Always appreciated!
Anna

[JohnTopley]

Try .... in B3

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!A:A"),A3,INDIRECT("'"&Sheets&"'!B:B")))

Entered with Ctrl+Shift+Enter

"Sheets" is a named range which has the names of worksheets e.g VP Marketing, Insurance, Wealth

Note there a match on the items in column A which Appear twice in the sheets to be summed so if there is value in B4 as well as the sub-total you will get a wrong result.

See the attached.

[daffodil11]

Totally doable, with a few minor alterations.

First, you need unique identifications to key off of. I added " Total" to each descriptor, both on your summary and the bottom line of each block.
Second, you need to make a list of the tab names somewhere, select those cells, and then define that range of cells with a Name in the Formula->Name Manager.
Lastly, you need to make sure you watch for inconsistencies. Your tab VP Marketing has a trailing space, which can throw off things. I removed it in my version.

B3:
=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!A15:A158"),$A3,INDIRECT("'"&MySheets&"'!B15:b158")))

C3:
=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!A15:A158"),$A3,INDIRECT("'"&MySheets&"'!c15:c158")))

D3:
=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!A15:A158"),$A3,INDIRECT("'"&MySheets&"'!c15:c158")))

and copy all three formulas down.

[niks28]

Hi,
Please find the attached info sheet for your ref

Cheers!

[AnnaG87]

Hi - I think this will work but I'm wondering, will the results only pull from the 3 columns (B,C,D)? On my full report that I'm working with, there may be numbers on the "total lines" for each account, so I only want to pull from those last three columns.

Thanks for your help!!

[daffodil11]

The formulas I used only pulled from B C and D. You'd just need to make sure you used unique identifiers to only pull the data you need.