Summary sheet for a list of sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Klips - 04 Apr 2007 01:00 GMT

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.

Reply to this Message

Lori - 04 Apr 2007 09:36 GMT

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.

> Hi all,
>
[quoted text clipped - 10 lines]
>
> Thanks in advance for all/any advice.

Reply to this Message

Rate this thread: