# Total of column since the previous total

#### Herostrata

##### New Member
Hi All,

Long time reader, first time poster.

I'm looking for a formula that will give me the sum for a range of cells since the last time that range was summed. Let me explain:

I have a variable list being output. Column A will be a list of items, Column B is the numerical value. Occasionally column A will read "Total" and when it does I want column C to give a sum of column A between the "Total" values. e.g.:

 A B C Rent 600​ Car 300​ Food 150​ Total 1050​ Rent 600​ Food 200​ Total 800​ Car 450​ Food 100​ Total 550​ Food 150​ Total 150​

I'm simplifying heavily and I think there's something I can do with an INDEX / MATCH combination but can't quite get it to work.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=IF(A2="Total",SUM(B\$2:B2)-SUM(C\$1:C1),"")``

#### Herostrata

##### New Member
Hi Fluff,
Thanks for the reply. This would work for the simplified version, but in the full document I need something that I can use in conjunction with other formulas in column C. I definitely over simplified for my example.

What I'm really looking for is closer to: IF A2 = "Total" [find the total of unknown number of above cells in C] ELSE SUM(A2*VLOOKUP(A2,array,index)

Without dumping my whole workbook here it looks more like this at the minute:

ABCDEF
Rent
600​
108​
Reference
Car
300​
45​
Rent
0.2​
Food
150​
27​
Car
0.15​
Total
1050​
Food
0.18​
Rent
600​
108​
Food
200​
36​
Total
800​
Car
450​
67.5​
Food
100​
18​
Total
550​
Food
150​
27​
Total
150​

In C3 I have "=SUM(B3*VLOOKUP(A3,\$E\$2:\$F\$4,2))"

In C4 I need something that will total C1:C3. The same in C7 should give the total for C5:C6.

#### Fluff

##### MrExcel MVP, Moderator
Why not just put it in col D
+Fluff 1.xlsm
ABCDEF
1
2Rent600120 Reference
3Car30045 Rent0.2
4Food15027 Car0.15
5Total1050 192Food0.18
6Rent600120
7Food20036
8Total800 156
9Car45067.5
10Food10018
11Total550 85.5
12Food15027
13Total150 27
Lists
Cell Formulas
RangeFormula
C2:C13C2=IF(A2="total","",SUM(B2*VLOOKUP(A2,\$E\$3:\$F\$5,2,0)))
D2:D13D2=IF(A2="Total",SUM(C\$2:C2)-SUM(D\$1:D1),"")

#### Herostrata

##### New Member

Why not just put it in col D
+Fluff 1.xlsm
ABCDEF
1
2Rent600120 Reference
3Car30045 Rent0.2
4Food15027 Car0.15
5Total1050 192Food0.18
6Rent600120
7Food20036
8Total800 156
9Car45067.5
10Food10018
11Total550 85.5
12Food15027
13Total150 27
Lists
Cell Formulas
RangeFormula
C2:C13C2=IF(A2="total","",SUM(B2*VLOOKUP(A2,\$E\$3:\$F\$5,2,0)))
D2:D13D2=IF(A2="Total",SUM(C\$2:C2)-SUM(D\$1:D1),"")
Why not just put it in col D
+Fluff 1.xlsm
ABCDEF
1
2Rent600120 Reference
3Car30045 Rent0.2
4Food15027 Car0.15
5Total1050 192Food0.18
6Rent600120
7Food20036
8Total800 156
9Car45067.5
10Food10018
11Total550 85.5
12Food15027
13Total150 27
Lists
Cell Formulas
RangeFormula
C2:C13C2=IF(A2="total","",SUM(B2*VLOOKUP(A2,\$E\$3:\$F\$5,2,0)))
D2:D13D2=IF(A2="Total",SUM(C\$2:C2)-SUM(D\$1:D1),"")
Hi Fluff,

In short because the document I'm working has a column B and C per month and I can't change the layout too much without affecting other files. It's an annual report that is output with costs per item per month and summarised totals. The report spans several groups and not all groups include the same costed items, hence some will have only 2 costed items per month and some will have 4-5, but all will have a "Total". Those figures need to be put through a multiplier and then re-totalled in the same column so I can show total for item cost per year and total of all costed items per month.

It's a laborious job to do manually so I'm trying to put a template together that will fill the multiplied columns automatically. Hopefully this screencap below will give you an idea of the scope. Everything under the "Cost" header is fixed in the output file, there's nothing I can change in how it's output. This goes into other documents The columns under "After Multiplier" are where I have room to make changes as part of my template.

#### Fluff

##### MrExcel MVP, Moderator
+Fluff 1.xlsm
ABCDEF
1
2Rent600120Reference
3Car30045Rent0.2
4Food15027Car0.15
5Total1050192Food0.18
6Rent600120
7Food20036
8Total800156
9Car45067.5
10Food10018
11Total55085.5
12Food15027
13Total15027
Lists
Cell Formulas
RangeFormula
C2:C13C2=IF(A2="total",SUM(INDEX(C\$1:C1,IFNA(XMATCH("Total",A\$1:A1,0,-1)+1,1)):C1,C\$1),SUM(B2*VLOOKUP(A2,\$E\$3:\$F\$5,2,0)))

#### Herostrata

##### New Member
+Fluff 1.xlsm
ABCDEF
1
2Rent600120Reference
3Car30045Rent0.2
4Food15027Car0.15
5Total1050192Food0.18
6Rent600120
7Food20036
8Total800156
9Car45067.5
10Food10018
11Total55085.5
12Food15027
13Total15027
Lists
Cell Formulas
RangeFormula
C2:C13C2=IF(A2="total",SUM(INDEX(C\$1:C1,IFNA(XMATCH("Total",A\$1:A1,0,-1)+1,1)):C1,C\$1),SUM(B2*VLOOKUP(A2,\$E\$3:\$F\$5,2,0)))
Amazing, thank you!

It took me a while to reverse engineer that but I've applied it to my template and it's working perfectly. Thanks very much for your help!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
4
Views
123
Replies
2
Views
165
Replies
1
Views
183
Replies
3
Views
368
Replies
65
Views
877

1,147,748
Messages
5,742,978
Members
423,769
Latest member
LongToast

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