Sumproduct for looking up and summing a range of information across multiple tabs?

[fanboy]

Hi everybody,

I have a workbook with a summary sheet that needs to be populated by a number of info tabs onto which information is updated manually.

• The info tabs are in the same format as the summary tab, with item descriptions in column A, and data relating to this in columns C to N (the data is spend per month on each item in column A).
• While the format of each info tab is the same, the items in the description column change.

I could manually link each summary sheet cell to all the related info tabs ('INFO1'!C6+'INFO2'!C6+....), or do something similar with vlookup, but this would take a long time to do, take loads of CPU time to process recalculation, and need to be updated regularly if new items or tabs were added.

I'm sure there is a way to use sumproduct to search all tabs in the workbook for relevant information and sum it on the summary tab using a fairly short formula that would still work if the info or summary tabs were updated, and if new sheets were added. However, I've got rather rusty and am struggling to work out how to do this. I'd appreciate any help.

Thanks for your help guys,

 

[fanboy]

I think I've figured it out. I'm using

=SUMPRODUCT(SUMIF(INDIRECT("'" & INDEX(Tabs,,) & "'!$A:$A"),$A6,INDIRECT("'" & INDEX(Tabs,,) & "'!C:C")))

Where column A is where the column with the name to be looked up, A6 contains the description to be looked up in column A, and Column C contains the value to be summed. Tabs is a list of the tabs on the spreadsheet that are to be looked up.

 

[Ingolf]

If you put al name of sheets in - say - col Z starting from Z2 to Zn then in your formula change

=SUMPRODUCT(SUMIF(INDIRECT("'" & Z2:Zn& "'!$A:$A"),$A6,INDIRECT("'" & Z2:Zn& "'!C:C")))

or make dynamic name range, replace Z2:Zn with that name range

 

[fanboy]

Thanks so much Ingolf. That tidies things up nicely