Summary sheet for a list of sheets

[Klips]

Hi all,

I have a workbook with multiple sheets that have similar datasets on
them. I'm trying to create a summary sheet that adds up the value of
the cells across the sheets and lists them on one sheet. I have
created a list of all the sheets I want to include (and have created a
dynamic range to represent the list). I'm having trouble trying to
write a formula that would take care of this by using the my list of
sheets and summing all the values that have the same ID numbers.

I'm not sure if this makes any sense. If you need more information or
would like to take a look at the file just let me know.

Thanks in advance for all/any advice.

 

[Lori]

If you have a list of IDs on the first column of each sheet and you
want to sum values in the second column for each ID on the summary
sheet, try filling down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Shts&"'!a:a"),A1,INDIRECT("'"&Shts&"'!
b:b")))

Where Shts is your range containing the sheet names.

One way to get a list of all IDs on to the summary sheet is to choose
Data > Consolidate with the options:

Function: Count
References: Sheet1!$A:$B, Sheet2!$A:$B, Sheet3!$A:$B, ...
Use Labels in: Left column

then click OK. If you use "Sum" for the summary function you should
get the same results as the formula above.

Another possibility is to use a PivotTable with the "multiple
consolidation ranges" option.