Cumulative Sums based on multiple fields

[Milan05]

Hi,

I have the following data:

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!

 

[Toadstool]

Hi Milan05,

Try this in L2 and copy down

=IF(B2<>"Summary","",SUMIFS($K1:$K$2,$B1:$B$2,"<>"&B2,$C1:$C$2,C2))

 

[kweaver]

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.

 

[Milan05]

Hi Milan05,

Try this in L2 and copy down

=IF(B2<>"Summary","",SUMIFS($K1:$K$2,$B1:$B$2,"<>"&B2,$C1:$C$2,C2))

Wow, smashed it. Awesome, thanks so much!!!!

 

[jtakw]

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

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

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4
5		Summary	EFGH								254.51	254.51
6
7
8		Summary	EFGH								26.46	280.97
9
10
11
12
13
14		Summary	EFGH								203.42	484.39
15
16		Summary	ABCD								1.65	1.65
17
18
19
20
21
22
23
24
25		Summary	ABCD								66.29	67.94
26
27
28
29
30
31		Summary	ABCD								30.68	98.62
Sheet922

Cell Formulas
Range	Formula
L2:L31	L2	=IF(B2="Summary",SUMIFS(K$2:K2,B$2:B2,"Summary",C$2:C2,C2),"")