Cumulative Sums based on multiple fields

Milan05

New Member
Joined
Apr 15, 2021
Messages
5
Hi,

I have the following data:

1618533362952.png


The orange highlighted rows are summary rows of all of the data above it.
The yellow highlighted fields are sum fields (plus a unique formula relevant to my use case). Essentially the yellow highlighted fields are sums of all the data in rows above it up until the next 'Summary' column.
For example K5 is calculated by summing 131.12 + 106.624 + 16.7621...

What I would like is that as I go down the worksheet, a formula which sums the highlighted yellow scores of those scores with the same 'code', where the code field is column C.
For example I would like L5 to identify that B5 is a Summary field, identify the code from C5, and therefore be equal to K5 (because this is the top of the workbook).

Now as I move down, L8 should be equal to K5 + K8. As row 8 has the same 'code' as row 5.

L14 = K5 + K8 + K14

L16 = K16 (different code in column C)

L25 = K25 + K16

L31 = K31 + K25 + K16

So essentially, a formula that reads from top to bottom of a worksheet, identifies 'Summary' rows AND identifies the 'code'. Grabs the summary score of that row (if the code is the same), and then adds it going down the worksheet.

So L5 should equal 254.50....

L8 should equal 254.50 + 26.45

etc.

Any help would be awesome.

Thanks!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,149
Office Version
  1. 2016
Platform
  1. Windows
Hi Milan05,

Try this in L2 and copy down
Excel Formula:
=IF(B2<>"Summary","",SUMIFS($K1:$K$2,$B1:$B$2,"<>"&B2,$C1:$C$2,C2))
 
Solution

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,134
Office Version
  1. 365
  2. 2010
Welcome to Mr Excel:

It would help greatly if (a) you indicated what version of Excel you run and (b) used XL2BB to post your sample data.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I agree with @kweaver , for future reference, please post samples using XL2BB, so we don't have to recreate your data manually for testing, see my signature for where and how.

For some reason Post #2 formula didn't work on my test sample, but seems it worked for you :unsure:

Anyway, you can also try this in L2 copied down:

Book3.xlsx
ABCDEFGHIJKL
1
2 
3 
4 
5SummaryEFGH254.51254.51
6 
7 
8SummaryEFGH26.46280.97
9 
10 
11 
12 
13 
14SummaryEFGH203.42484.39
15 
16SummaryABCD1.651.65
17 
18 
19 
20 
21 
22 
23 
24 
25SummaryABCD66.2967.94
26 
27 
28 
29 
30 
31SummaryABCD30.6898.62
Sheet922
Cell Formulas
RangeFormula
L2:L31L2=IF(B2="Summary",SUMIFS(K$2:K2,B$2:B2,"Summary",C$2:C2,C2),"")
 

Forum statistics

Threads
1,147,451
Messages
5,741,197
Members
423,648
Latest member
steel1968

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
Top