Sum Subtotals Based on numbered subsections

S

sameri

New Member
Joined
May 15, 2024
Messages
7
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
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)

1715821283448.png
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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).
1715826236327.png
 
Upvote 0
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
ABC
6Item NoInput LevelFormula
74.460
84.4.160
94.4.1.160
104.4.1.1.1100
114.4.1.1.2100
124.4.1.1.3400
134.4.2.1100
144.4.2.1.11000
Sheet1
Cell Formulas
RangeFormula
C7:C14C7=SUMIFS(C:C,A:A,$A7&".?")+SUMIFS(B:B,A:A,$A7&".?")
 
Upvote 0
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.
 
Upvote 0
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"

TASKPer 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
 
Upvote 0
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
ABC
6Item NoInput LevelFormula
74.460
84.4.160
94.4.1.160
104.4.1.1.1100
114.4.1.1.2100
124.4.1.1.3400
134.4.2.1100
144.4.2.1.11000
Sheet1
Cell Formulas
RangeFormula
C7:C14C7=SUMIFS(C:C,A:A,$A7&".?")+SUMIFS(B:B,A:A,$A7&".?")
Click to expand...
Cheers for the heads up... Only needed to run in 365 so all good.
 
Upvote 0
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"

TASKPer 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
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Formula to Sum the values of a column based on multiple criteria
Replies
1
Views
591
AFPathfinder
A
O
  • Question
Macro to calculate grouped investments values based on selected month
Replies
0
Views
536
OET1982
O
F
  • Question
Creating a dynamic chart range over a table with cells made blank via a formula
Replies
0
Views
2K
FDIOPY
F
C
  • Question
Is Excel Online unable to Dynamically SUM/Subtotal?
Replies
0
Views
447
cmphares
C
R
  • Question
Automate hide-unhide button based on heading style
Replies
0
Views
518
rahildhody
R

Forum statistics

Threads
1,216,725
Messages
6,132,342
Members
449,719
Latest member
excel4mac

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top