Sum Subtotals Based on numbered subsections

[sameri]

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)

 

[Alex Blakenburg]

How did you get from the previously posted formulas to that one ?
It doesn't work.
The following are incorrect:

20240516 SumIfs sameri.xlsx
	A	B	C	D	E	F	G
1	TASK	Per Line	Subs	Final S/Be		OPs Final	Difference
2		(Hidden)	(Hidden)
3	4		$98,658	$98,658		$364,438	-$265,780
4	4.1		$35,136	$35,136		$35,136	$0
5	4.1.1	$16,652	$0	$16,652		$16,652	$0
6	4.1.2	$16,494	$0	$16,494		$16,494	$0
7	4.1.3	$1,990	$0	$1,990		$1,990	$0
8	4.2		$9,988	$9,988		$9,988	$0
9	4.2.1	$6,644	$0	$6,644		$6,644	$0
10	4.2.2		$3,344	$3,344		$3,344	$0
11	4.2.2.1	$704	$0	$704		$704	$0
12	4.2.2.2	$2,640	$0	$2,640		$2,640	$0
13	4.3		$3,344	$3,344		$3,344	$0
14	4.3.1		$3,344	$3,344		$3,344	$0
15	4.3.1.1	$704	$0	$704		$704	$0
16	4.3.1.2	$1,408	$0	$1,408		$1,408	$0
17	4.3.1.3	$1,232	$0	$1,232		$1,232	$0
18	4.4		$50,190	$50,190		$264,358	-$214,168
19	4.4.1		$50,190	$50,190		$114,342	-$64,152
20	4.4.1.1	$14,080	$0	$14,080		$14,080	$0
21	4.4.1.2	$2,846	$0	$2,846		$2,846	$0
22	4.4.1.3	$2,816	$0	$2,816		$2,816	$0
23	4.4.1.4		$15,488	$15,488		$15,488	$0
24	4.4.1.4.1	$2,112	$0	$2,112		$2,112	$0
25	4.4.1.4.2	$4,928	$0	$4,928		$4,928	$0
26	4.4.1.4.3	$5,632	$0	$5,632		$5,632	$0
27	4.4.1.4.4	$2,816	$0	$2,816		$2,816	$0
28	4.4.1.5		$14,960	$14,960		$21,120	-$6,160
29	4.4.1.5.1		$14,960	$14,960		$14,960	$0
30	4.4.1.5.1.1	$3,432	$0	$3,432		$3,432	$0
31	4.4.1.5.1.2		$0	$0			$0
32	4.4.1.5.1.3	$1,408	$0	$1,408		$1,408	$0
33	4.4.1.5.1.4	$1,408	$0	$1,408		$1,408	$0
34	4.4.1.5.1.5	$880	$0	$880		$880	$0
35	4.4.1.5.1.6	$3,696	$0	$3,696		$3,696	$0
36	4.4.1.5.1.7	$704	$0	$704		$704	$0
37	4.4.1.5.1.8	$440	$0	$440		$440	$0
38	4.4.1.5.1.9	$704	$0	$704		$704	$0
39	4.4.1.5.1.10	$1,056	$0	$1,056		$1,056	$0
40	4.4.1.5.1.11	$528	$0	$528		$528	$0
41	4.4.1.5.1.12	$704	$0	$704		$704	$0
Sheet2

Cell Formulas
Range	Formula
C3:C41	C3	=SUM(SUMIFS(B:B,A:A,A3&{".?",".??"})+SUMIFS(C:C,A:A,A3&{".?",".??"}))
D3:D41	D3	=B3+C3
G3:G41	G3	=D3-F3

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$A$2:$G$41	C3:C41

 

[sameri]

How did you get from the previously posted formulas to that one ?
It doesn't work.
The following are incorrect:

View attachment 111490

20240516 SumIfs sameri.xlsx
	A	B	C	D	E	F	G
1	TASK	Per Line	Subs	Final S/Be		OPs Final	Difference
2		(Hidden)	(Hidden)
3	4		$98,658	$98,658		$364,438	-$265,780
4	4.1		$35,136	$35,136		$35,136	$0
5	4.1.1	$16,652	$0	$16,652		$16,652	$0
6	4.1.2	$16,494	$0	$16,494		$16,494	$0
7	4.1.3	$1,990	$0	$1,990		$1,990	$0
8	4.2		$9,988	$9,988		$9,988	$0
9	4.2.1	$6,644	$0	$6,644		$6,644	$0
10	4.2.2		$3,344	$3,344		$3,344	$0
11	4.2.2.1	$704	$0	$704		$704	$0
12	4.2.2.2	$2,640	$0	$2,640		$2,640	$0
13	4.3		$3,344	$3,344		$3,344	$0
14	4.3.1		$3,344	$3,344		$3,344	$0
15	4.3.1.1	$704	$0	$704		$704	$0
16	4.3.1.2	$1,408	$0	$1,408		$1,408	$0
17	4.3.1.3	$1,232	$0	$1,232		$1,232	$0
18	4.4		$50,190	$50,190		$264,358	-$214,168
19	4.4.1		$50,190	$50,190		$114,342	-$64,152
20	4.4.1.1	$14,080	$0	$14,080		$14,080	$0
21	4.4.1.2	$2,846	$0	$2,846		$2,846	$0
22	4.4.1.3	$2,816	$0	$2,816		$2,816	$0
23	4.4.1.4		$15,488	$15,488		$15,488	$0
24	4.4.1.4.1	$2,112	$0	$2,112		$2,112	$0
25	4.4.1.4.2	$4,928	$0	$4,928		$4,928	$0
26	4.4.1.4.3	$5,632	$0	$5,632		$5,632	$0
27	4.4.1.4.4	$2,816	$0	$2,816		$2,816	$0
28	4.4.1.5		$14,960	$14,960		$21,120	-$6,160
29	4.4.1.5.1		$14,960	$14,960		$14,960	$0
30	4.4.1.5.1.1	$3,432	$0	$3,432		$3,432	$0
31	4.4.1.5.1.2		$0	$0			$0
32	4.4.1.5.1.3	$1,408	$0	$1,408		$1,408	$0
33	4.4.1.5.1.4	$1,408	$0	$1,408		$1,408	$0
34	4.4.1.5.1.5	$880	$0	$880		$880	$0
35	4.4.1.5.1.6	$3,696	$0	$3,696		$3,696	$0
36	4.4.1.5.1.7	$704	$0	$704		$704	$0
37	4.4.1.5.1.8	$440	$0	$440		$440	$0
38	4.4.1.5.1.9	$704	$0	$704		$704	$0
39	4.4.1.5.1.10	$1,056	$0	$1,056		$1,056	$0
40	4.4.1.5.1.11	$528	$0	$528		$528	$0
41	4.4.1.5.1.12	$704	$0	$704		$704	$0
Sheet2

Cell Formulas
Range	Formula
C3:C41	C3	=SUM(SUMIFS(B:B,A:A,A3&{".?",".??"})+SUMIFS(C:C,A:A,A3&{".?",".??"}))
D3:D41	D3	=B3+C3
G3:G41	G3	=D3-F3

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$A$2:$G$41	C3:C41

I am royally screwing up my first post here aren't I! I ommitted a heap of data in my snippet. I just got excited that it was working....

Let me try again:

ID	Per Line (Hidden)	Subs (Hidden)	Estimated Final Cost
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
4.4.1.5.2	$ -	$ 6,160	$ 6,160
4.4.1.5.2.1	$ 1,584	$ -	$ 1,584
4.4.1.5.2.2	$ -	$ -	$ -
4.4.1.5.2.3	$ 264	$ -	$ 264
4.4.1.5.2.4	$ 264	$ -	$ 264
4.4.1.5.2.5	$ 264	$ -	$ 264
4.4.1.5.2.6	$ 2,112	$ -	$ 2,112
4.4.1.5.2.7	$ 264	$ -	$ 264
4.4.1.5.2.8	$ 264	$ -	$ 264
4.4.1.5.2.9	$ 264	$ -	$ 264
4.4.1.5.2.10	$ 264	$ -	$ 264
4.4.1.5.2.11	$ 264	$ -	$ 264
4.4.1.5.2.12	$ 352	$ -	$ 352
4.4.1.6	$ -	$ 6,864	$ 6,864
4.4.1.6.1	$ -	$ 4,576	$ 4,576
4.4.1.6.1.1	$ 1,408	$ -	$ 1,408
4.4.1.6.1.2	$ 1,408	$ -	$ 1,408
4.4.1.6.1.3	$ 1,408	$ -	$ 1,408
4.4.1.6.1.4	$ 352	$ -	$ 352
4.4.1.6.2	$ -	$ 2,288	$ 2,288
4.4.1.6.2.1	$ 704	$ -	$ 704
4.4.1.6.2.2	$ 704	$ -	$ 704
4.4.1.6.2.3	$ 704	$ -	$ 704
4.4.1.6.2.4	$ 176	$ -	$ 176
4.4.1.7	$ -	$ 11,264	$ 11,264
4.4.1.7.1	$ -	$ 7,744	$ 7,744
4.4.1.7.1.1	$ 1,408	$ -	$ 1,408
4.4.1.7.1.2	$ 880	$ -	$ 880
4.4.1.7.1.3	$ 3,520	$ -	$ 3,520
4.4.1.7.1.4	$ 1,408	$ -	$ 1,408
4.4.1.7.1.5	$ 528	$ -	$ 528
4.4.1.7.2	$ -	$ 3,520	$ 3,520
4.4.1.7.2.1	$ 704	$ -	$ 704
4.4.1.7.2.2	$ 528	$ -	$ 528
4.4.1.7.2.3	$ 1,408	$ -	$ 1,408
4.4.1.7.2.4	$ 704	$ -	$ 704
4.4.1.7.2.5	$ 176	$ -	$ 176
4.4.1.8	$ -	$ 39,864	$ 39,864
4.4.1.8.1	$ -	$ 7,216	$ 7,216
4.4.1.8.1.1	$ 1,232	$ -	$ 1,232
4.4.1.8.1.2	$ 2,112	$ -	$ 2,112
4.4.1.8.1.3	$ 2,288	$ -	$ 2,288
4.4.1.8.1.4	$ 1,232	$ -	$ 1,232
4.4.1.8.1.5	$ 352	$ -	$ 352
4.4.1.8.2	$ -	$ 32,648	$ 32,648
4.4.1.8.2.1	$ 616	$ -	$ 616
4.4.1.8.2.2	$ 616	$ -	$ 616
4.4.1.8.2.3	$ 1,232	$ -	$ 1,232
4.4.1.8.2.4	$ 616	$ -	$ 616
4.4.1.8.2.5	$ 24,640	$ -	$ 24,640
4.4.1.8.2.6	$ 4,224	$ -	$ 4,224
4.4.1.8.2.7	$ 704	$ -	$ 704
4.4.2	$ -	$ 150,016	$ 150,016
4.4.2.1	$ 6,336	$ -	$ 6,336
4.4.2.2	$ 25,952	$ -	$ 25,952
4.4.2.3	$ 2,816	$ -	$ 2,816
4.4.2.4	$ 1,760	$ -	$ 1,760
4.4.2.5	$ -	$ 73,728	$ 73,728
4.4.2.5.1	$ 14,080	$ -	$ 14,080
4.4.2.5.2	$ 16,448	$ -	$ 16,448
4.4.2.5.3	$ 15,744	$ -	$ 15,744
4.4.2.5.4	$ 11,264	$ -	$ 11,264
4.4.2.5.5	$ 10,560	$ -	$ 10,560
4.4.2.5.6	$ 5,632	$ -	$ 5,632
4.4.2.6	$ 15,488	$ -	$ 15,488
4.4.2.7	$ 4,224	$ -	$ 4,224
4.4.2.8	$ 16,896	$ -	$ 16,896
4.4.2.9	$ 2,112	$ -	$ 2,112
4.4.2.10	$ 704	$ -	$ 704
4.5	$ -	$ 51,612	$ 51,612
4.5.1	$ 7,436	$ -	$ 7,436
4.5.2	$ 10,560	$ -	$ 10,560
4.5.3	$ 7,040	$ -	$ 7,040
4.5.4	$ -	$ 26,576	$ 26,576
4.5.4.1	$ 5,632	$ -	$ 5,632
4.5.4.2	$ 12,672	$ -	$ 12,672
4.5.4.3	$ 2,816	$ -	$ 2,816
4.5.4.4	$ 880	$ -	$ 880
4.5.4.5	$ 4,224	$ -	$ 4,224
4.5.4.6	$ 352	$ -	$ 352