Summing Sheet summaries into one sheet

Kurt Robohm

I have multiple sheets, each with the same summary items that are derived by formulas that use data in the sheet. I need to create a one row sheet that sums all the of the same summary items from each sheet so that I can use the charting function to show a donut chart based on the summed values for a portfolio dashboard.

Only way I have been able to figure out how to do this is to re-create the same formulas that were used in the sheet summaries to produce the same sums within a sheet using a range reference for each of the columns and sheets that I need to sum up. This seems rather inefficient since all I should really need to do is sum all the sheet summary items that were created with the original formulas.

Help info I have found indicated to create a portfolio view report that included all the sheet summaries, but that results in a multi-row grid, that does not allow me to use the donut chart function (donut chart seems to only work with a single row sheet).

Is there a way of referencing the sheet summary items in a formula so that I could write a simple formula something like this?

=Sum({sheet 1[}[late task count]#,{sheet 2}[late task count]#, {sheet 3}[late task count]#)

Genevieve P.

Hi @Kurt Robohm

Creating a Sheet Summary Report is the way to do this!

It will initially bring in all the Summary Fields as one row per-sheet, as you've found, but then you can Group and Summarize those rows in the Report to become the source for your chart.

Note that you need to have some sort of Grouping applied, so what I'd recommend doing is set up an identical field in all your sheets that has an identical value - e.g. "For Report". Then you can Group by this helper value, and use the Summarize function on the other fields for your calculations.

Let me know if this makes sense or if you'd like to see screen captures.

Cheers,

Genevieve

Kurt Robohm

Thank you Genevieve, that worked. I had previously just totaled the rows and the total was not picked up in the charting function so I figured sorting would give the same result. When I tried the sort function, poof !, it kicked in and worked.

Genevieve P.

Wonderful, I'm glad it worked for you! 🙂