How to Sum Across Multiple Sheets?

Daryl Riggins

Hi,

I have six sheets with an identical setup. I want to be able to summarize how much Effort each person has been assigned for all the sheets. For example, Bugs Bunny may have 12 hours assigned in Sheet 1, 4 hours in Sheet 3, and 24 in Sheet 5 for a grand total of 40.

I want to be able to summarize total Effort for each person, so I would end up with grand totals for Bugs Bunny, Daryl Riggins, and Pepe Lepew.

Running a report doesn't work as it returns individual rows from sheets, not totals. In the screenshot below, I want the total of 8 hours Effort assigned to Daryl, not two separate rows of 2 and 6 hours.

I've looked into using SUMIF and referencing the relevant sheets, but it appears I cannot reference more than 1 sheet, so that's a dead end.

Thoughts on how to accomplish this kind of summary reporting?

Screenshot 2020-09-21 141159.jpg

David Joyeuse

Hi @Daryl Riggins

You can do a SUM of SUMIFs or so :)

=SUMIF({Sheet One}) + SUMIF({Sheet 2}) + SUMIF({Sheet 3}) + and so on...

or:

=SUM(SUMIF({Sheet 1}), SUMIF({Sheet 2}), SUMIF({Sheet 3}), ...)

Hope it helped!

Daryl Riggins

I see how that could work, but I’m concerned about the durability of the solution. Sheets are going to be added and removed as time goes on. And every time that happens, these formulas will break, correct?

Shawn Leahy

This is an old string, so I am fishing here...

Daryl, did you find an answer or come up with a work around?

I am interested in a better solution to this issue as well. We run separate Sheets for each of our projects with identical information, and sheets are created for each NEW project. I would like to sum like information in like columns across all sheets without having to update the formula each time a NEW project Sheet is created.

At any given time I could have 16 - 20 projects running (I haven't tried SUMIFS on more than 7 sheets, not even sure if it would work for the quantity of sheets I have)

It would be nice if I could just select the Folder that contains the like sheets, then have the option to select Ranges and Criterion from the common headers... similar to the feature in Reports, automatically updating to the the sheets within the folder.

Either that or the ability to sum to a field within a report...

Any help would be appreciated.

Thanks!

ariestauro

Hi,

I'm looking for a solution to the same problem. Hoping someone can provide some help!