# Cumulative Sums based on multiple fields

#### Milan05

##### New Member
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!

### 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.

##### Well-known Member
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))``

#### kweaver

##### Well-known Member
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

##### New Member
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))``
Wow, smashed it. Awesome, thanks so much!!!!

#### jtakw

##### Well-known Member
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
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),"")

Replies
1
Views
282
Replies
6
Views
93
Replies
4
Views
196
Replies
5
Views
319
Replies
7
Views
172

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.

### Which adblocker are you using?

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

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