Show breakdown of a SUM formula

[mvb83]

Hi,

I have a worksheet containing a large amount of data and then another worksheet made up of summary tables. The summary table uses SUM on various fields in the data to get monthly totals.

For example one cell is

=SUM(Data!C7524,Data!C7530,Data!C7536,Data!C7542,Data!C7548,Data!C7556)

Some of the other sums can be made up of 100+ cells.

I would like to be able to keep the sum in the cell but somehow get a breakdown of what that sum is made up of. So I could click on the cell and ideally it would create a new worksheet listing all the criteria values and description for that sum.

So clicking on the one above would give me something like

JP7600 100.00
JS0140 300.00
JS0340 100.00
JS0540 50.00
JS0740 75.00
JS2030 100.00

I've attached an example of what I have.

Hope that all makes sense. Is what I'm after possible? I appreciate any help offered!

Thanks,
Martin

[dilipandey]

Hi Martin and welcome to the forum.

If you want to see the numbers getting summed in the formula, select the bold area of the formula and press F9 :-

=SUM(Data!C7524,Data!C7530,Data!C7536,Data!C7542,Data!C7548,Data!C7556)

Now you'll see the numbers getting summed and Sheet information you already know


Regards,
DILIPandey
<click on below * if this helps>

[mvb83]

Hi DILIPandey,

Thanks for the welcome, and the quick reply!

We use that at the moment which is fine for the smaller sums but on the data sheet there is also a description field as well as the value and it's this ideally we want to be able to get a breakdown of.

Ideally I'd like to be able to click on Retail Sales and see each line that makes up Retail Sales and the description.

I hope i'm making sense.

Thanks,
Martin