Sum Subtotals Based on numbered subsections

sameri · May 15, 2024

Hi All!

First time post so hopefully I get ti right!

I've tried a few methods but finding myself getting stuck. I need to be able to dynamically sum values based on subsection numbering.

Note:

The numbers shown in column A are static pasted values from another export.
The formulas in columns S, T and U all need to be dynamic as this sheet is hopefully going to be used a template. i.e. simply entering a heap of SUM formulas into the relevant cells won't suffice nor will manually grouping cells and adding a Subtotals as theses are all manual actions after pasting the data in.
Note that all of the colored formatting shown is dynamic Conditional Formatting based on comparing the LEN row x with x+1 in Column A - this won't work for my purposes as there are obviously nested subsections in column A.
Trying to achieve this without VBA as we are attempting to keep the workbook without macros/scripts.
Examples of what I'm trying to achieve:
- In cell U25, show the sum of all 4.4.1 items, i.e. U29+U34.
- In cell U34, show the sum of all 4.4.1.5 items, i.e. U35+U48
- In cell U35, show the sum of all 4.4.1.5.1 items, i.e. SUM(U36:U47)

Tetra201 · May 15, 2024

Try in U35:

Excel Formula:

=SUM(SUMIFS(U:U,A:A,A35&{".?",".??"}))

sameri · May 15, 2024

Legend. That is so close to what I'm after that it will do... But...

This is what that formula returns versus what I'm after in an ideal world - summing ALL subsections. (I've moved data to a temp sheet to have a play).

Alex Blakenburg · May 16, 2024

That's a different layout to your original. I was wondering how you were hoping to make the original dynamic.
See if this works for you:
PS: The capabilities between 2019, 2021 & 365 are very different so it unless you need responses to cater for the lowest common denominator it would be good if you specified what version you want it to run on.

20240516 SumIfs sameri.xlsx

A

B

C

6

Item No

Input Level

Formula

7

4.4

60

8

4.4.1

60

9

4.4.1.1

60

10

4.4.1.1.1

10

0

11

4.4.1.1.2

10

0

12

4.4.1.1.3

40

0

13

4.4.2.1

100

14

4.4.2.1.1

100

0

Sheet1

Cell Formulas
Range	Formula
C7:C14	C7	=SUMIFS(C:C,A:A,$A7&".?")+SUMIFS(B:B,A:A,$A7&".?")

Tetra201 · May 16, 2024

Hmm, in my test file everything works as expected (Excel 2010, Excel 2021):

sameri · May 16, 2024

Tetra201 said:
Hmm, in my test file everything works as expected (Excel 2010, Excel 2021):
View attachment 111487

Yep yep... I see how that works but it means it cannot be as dynamic as I'd like as I need to place the formula in the correct cells in the same row and leave the static data alone.

Alex Blakenburg · May 16, 2024

I missed that @Tetra201 solution catered for double digits, however based on your revised format you would need to apply it to 2 columns eg.

Excel Formula:

=SUM(SUMIFS(B:B,A:A,A2&{".?",".??"})+SUMIFS(C:C,A:A,A2&{".?",".??"}))

PS: I don't know that only works for me because I am running 365 with dynamic arrays.

sameri · May 16, 2024

So my solution was a longwinded version of the above and I daresay there's a better way but this works:

What I did:

Formula in "Subs (Hidden)" column =SUM(SUMIFS(B:B,A:A,A2&{".?",".?.?",".?.?.?",".?.?.?.?",".?.?.?.?.?",".??",".?.??",".?.?.??",".?.?.?.??",".?.?.?.?.??"}))
Formula in "Final" column =B2+C2
Then I hide the columns marked "Hidden"

TASK	Per Line (Hidden)	Subs (Hidden)	Final
4	$ 364,438	$ 909,500	$ 364,438
4.1	$ -	$ 35,136	$ 35,136
4.1.1	$ 16,652	$ -	$ 16,652
4.1.2	$ 16,494	$ -	$ 16,494
4.1.3	$ 1,990	$ -	$ 1,990
4.2	$ -	$ 9,988	$ 9,988
4.2.1	$ 6,644	$ -	$ 6,644
4.2.2	$ -	$ 3,344	$ 3,344
4.2.2.1	$ 704	$ -	$ 704
4.2.2.2	$ 2,640	$ -	$ 2,640
4.3	$ -	$ 3,344	$ 3,344
4.3.1	$ -	$ 3,344	$ 3,344
4.3.1.1	$ 704	$ -	$ 704
4.3.1.2	$ 1,408	$ -	$ 1,408
4.3.1.3	$ 1,232	$ -	$ 1,232
4.4	$ -	$ 264,358	$ 264,358
4.4.1	$ -	$ 114,342	$ 114,342
4.4.1.1	$ 14,080	$ -	$ 14,080
4.4.1.2	$ 2,846	$ -	$ 2,846
4.4.1.3	$ 2,816	$ -	$ 2,816
4.4.1.4	$ -	$ 15,488	$ 15,488
4.4.1.4.1	$ 2,112	$ -	$ 2,112
4.4.1.4.2	$ 4,928	$ -	$ 4,928
4.4.1.4.3	$ 5,632	$ -	$ 5,632
4.4.1.4.4	$ 2,816	$ -	$ 2,816
4.4.1.5	$ -	$ 21,120	$ 21,120
4.4.1.5.1	$ -	$ 14,960	$ 14,960
4.4.1.5.1.1	$ 3,432	$ -	$ 3,432
4.4.1.5.1.2	$ -	$ -	$ -
4.4.1.5.1.3	$ 1,408	$ -	$ 1,408
4.4.1.5.1.4	$ 1,408	$ -	$ 1,408
4.4.1.5.1.5	$ 880	$ -	$ 880
4.4.1.5.1.6	$ 3,696	$ -	$ 3,696
4.4.1.5.1.7	$ 704	$ -	$ 704
4.4.1.5.1.8	$ 440	$ -	$ 440
4.4.1.5.1.9	$ 704	$ -	$ 704
4.4.1.5.1.10	$ 1,056	$ -	$ 1,056
4.4.1.5.1.11	$ 528	$ -	$ 528
4.4.1.5.1.12	$ 704	$ -	$ 704

sameri · May 16, 2024

Alex Blakenburg said:
That's a different layout to your original. I was wondering how you were hoping to make the original dynamic.
See if this works for you:
PS: The capabilities between 2019, 2021 & 365 are very different so it unless you need responses to cater for the lowest common denominator it would be good if you specified what version you want it to run on.

20240516 SumIfs sameri.xlsx
A B C
6 Item No Input Level Formula
7 4.4 60
8 4.4.1 60
9 4.4.1.1 60
10 4.4.1.1.1 10 0
11 4.4.1.1.2 10 0
12 4.4.1.1.3 40 0
13 4.4.2.1 100
14 4.4.2.1.1 100 0
Sheet1
Cell Formulas
Range Formula
C7:C14 C7 =SUMIFS(C:C,A:A,$A7&".?")+SUMIFS(B:B,A:A,$A7&".?")

Cheers for the heads up... Only needed to run in 365 so all good.

sameri · May 16, 2024

sameri said:
So my solution was a longwinded version of the above and I daresay there's a better way but this works:

What I did:

Formula in "Subs (Hidden)" column =SUM(SUMIFS(B:B,A:A,A2&{".?",".?.?",".?.?.?",".?.?.?.?",".?.?.?.?.?",".??",".?.??",".?.?.??",".?.?.?.??",".?.?.?.?.??"}))

Formula in "Final" column =B2+C2

Then I hide the columns marked "Hidden"

TASK Per Line
(Hidden) Subs
(Hidden) Final
4 $ 364,438 $ 909,500 $ 364,438
4.1 $ - $ 35,136 $ 35,136
4.1.1 $ 16,652 $ - $ 16,652
4.1.2 $ 16,494 $ - $ 16,494
4.1.3 $ 1,990 $ - $ 1,990
4.2 $ - $ 9,988 $ 9,988
4.2.1 $ 6,644 $ - $ 6,644
4.2.2 $ - $ 3,344 $ 3,344
4.2.2.1 $ 704 $ - $ 704
4.2.2.2 $ 2,640 $ - $ 2,640
4.3 $ - $ 3,344 $ 3,344
4.3.1 $ - $ 3,344 $ 3,344
4.3.1.1 $ 704 $ - $ 704
4.3.1.2 $ 1,408 $ - $ 1,408
4.3.1.3 $ 1,232 $ - $ 1,232
4.4 $ - $ 264,358 $ 264,358
4.4.1 $ - $ 114,342 $ 114,342
4.4.1.1 $ 14,080 $ - $ 14,080
4.4.1.2 $ 2,846 $ - $ 2,846
4.4.1.3 $ 2,816 $ - $ 2,816
4.4.1.4 $ - $ 15,488 $ 15,488
4.4.1.4.1 $ 2,112 $ - $ 2,112
4.4.1.4.2 $ 4,928 $ - $ 4,928
4.4.1.4.3 $ 5,632 $ - $ 5,632
4.4.1.4.4 $ 2,816 $ - $ 2,816
4.4.1.5 $ - $ 21,120 $ 21,120
4.4.1.5.1 $ - $ 14,960 $ 14,960
4.4.1.5.1.1 $ 3,432 $ - $ 3,432
4.4.1.5.1.2 $ - $ - $ -
4.4.1.5.1.3 $ 1,408 $ - $ 1,408
4.4.1.5.1.4 $ 1,408 $ - $ 1,408
4.4.1.5.1.5 $ 880 $ - $ 880
4.4.1.5.1.6 $ 3,696 $ - $ 3,696
4.4.1.5.1.7 $ 704 $ - $ 704
4.4.1.5.1.8 $ 440 $ - $ 440
4.4.1.5.1.9 $ 704 $ - $ 704
4.4.1.5.1.10 $ 1,056 $ - $ 1,056
4.4.1.5.1.11 $ 528 $ - $ 528
4.4.1.5.1.12 $ 704 $ - $ 704

Cannot figure out how/if I can edit my post but I should've omitted the first row "4" as the formula in the "Subs (Hidden)" column was incorrect - just a SUM formula hence it showing $900k

Sum Subtotals Based on numbered subsections

sameri

New Member

Excel Facts

Tetra201

MrExcel MVP

sameri

New Member

Alex Blakenburg

MrExcel MVP

Tetra201

MrExcel MVP

sameri

New Member

Alex Blakenburg

MrExcel MVP

sameri

New Member

sameri

New Member

sameri

New Member

Similar threads

Forum statistics

Share this page

Sum Subtotals Based on numbered subsections

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock