Sum Within Groups and Subgroups of Different Lengths

[H2theO]

Hi everyone,

I frequently work with data organized by groups and subgroups (literally, the +/- expand/collapse) and looking for a formula that can sum within each.

The main hurdles are 1) each group or subgroup are different lengths, and 2) each can have repeating names (e.g., both Group 1 and Group 2 might have an operational costs subgroup). At the moment, my team has to manually adjust SUM and SUMIF ranges rather than having one formula that can be copied, shown in F-I in the attached demo.

I've tried filling in each cell with its parent group and subgroup names and using xlookup or index-match, but run into the same issue that requires manually adjusting the formula to reference the right parent name.

Any suggestions are welcome, because this takes forever with our datasets!

[alansidman]

Would this presentation work for you?

Excel 2016 (Windows) 32 bit

	A	B	C
22	Column1	Column2	Totals
23	Group1	Sub1	120
24	Group1	Sub 2	41
25	Group2	Sub1	80
26	Group2	Sub2	27

Sheet: Sheet1

[H2theO]

Thanks for replying! There would need to be another column (multiple items within each Sub -- need to sum the items in each Sub and the Subs in each Group), but otherwise yes, setting it up with each row filled in is fine. Does that mean you know a formula that works with that presentation?

[alansidman]

For this presentation, I did this with Power Query. Here is the Mcode and background information. For your specific requirements, I will need to try to modify. Will not get to it until later tonight. Its Happy Hour Time in the 'Boat and I am running late.

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.


- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[alansidman]

Had a minute or two. Would this work for you?

Excel 2016 (Windows) 32 bit

	E	F	G	H	I
22	Column1	Column2	Column3	Totals	Column4
23	Group1	Sub1	Item1	120	90
24	Group1	Sub1	Item2	120	30
25	Group1	Sub 2	Item1	41	20
26	Group1	Sub 2	Item2	41	9
27	Group1	Sub 2	Item3	41	12
28	Group2	Sub1	Item1	80	60
29	Group2	Sub1	Item2	80	4
30	Group2	Sub1	Item3	80	16
31	Group2	Sub2	Item1	27	11
32	Group2	Sub2	Item2	27	8
33	Group2	Sub2	Item3	27	2
34	Group2	Sub2	Item4	27	6

Sheet: Sheet1