Total of column since the previous total

[Herostrata]

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.

Thanks in advance.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(A2="Total",SUM(B$2:B2)-SUM(C$1:C1),"")

 

[Herostrata]

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:

A	B	C	D	E	F
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]

Why not just put it in col D

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Rent	600	120		Reference
3	Car	300	45		Rent	0.2
4	Food	150	27		Car	0.15
5	Total	1050		192	Food	0.18
6	Rent	600	120
7	Food	200	36
8	Total	800		156
9	Car	450	67.5
10	Food	100	18
11	Total	550		85.5
12	Food	150	27
13	Total	150		27
Lists

Cell Formulas
Range	Formula
C2:C13	C2	=IF(A2="total","",SUM(B2*VLOOKUP(A2,$E$3:$F$5,2,0)))
D2:D13	D2	=IF(A2="Total",SUM(C$2:C2)-SUM(D$1:D1),"")

 

[Herostrata]

Why not just put it in col D

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Rent	600	120		Reference
3	Car	300	45		Rent	0.2
4	Food	150	27		Car	0.15
5	Total	1050		192	Food	0.18
6	Rent	600	120
7	Food	200	36
8	Total	800		156
9	Car	450	67.5
10	Food	100	18
11	Total	550		85.5
12	Food	150	27
13	Total	150		27
Lists

Cell Formulas
Range	Formula
C2:C13	C2	=IF(A2="total","",SUM(B2*VLOOKUP(A2,$E$3:$F$5,2,0)))
D2:D13	D2	=IF(A2="Total",SUM(C$2:C2)-SUM(D$1:D1),"")

Why not just put it in col D

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Rent	600	120		Reference
3	Car	300	45		Rent	0.2
4	Food	150	27		Car	0.15
5	Total	1050		192	Food	0.18
6	Rent	600	120
7	Food	200	36
8	Total	800		156
9	Car	450	67.5
10	Food	100	18
11	Total	550		85.5
12	Food	150	27
13	Total	150		27
Lists

Cell Formulas
Range	Formula
C2:C13	C2	=IF(A2="total","",SUM(B2*VLOOKUP(A2,$E$3:$F$5,2,0)))
D2:D13	D2	=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]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Rent	600	120		Reference
3	Car	300	45		Rent	0.2
4	Food	150	27		Car	0.15
5	Total	1050	192		Food	0.18
6	Rent	600	120
7	Food	200	36
8	Total	800	156
9	Car	450	67.5
10	Food	100	18
11	Total	550	85.5
12	Food	150	27
13	Total	150	27
Lists

Cell Formulas
Range	Formula
C2:C13	C2	=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]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Rent	600	120		Reference
3	Car	300	45		Rent	0.2
4	Food	150	27		Car	0.15
5	Total	1050	192		Food	0.18
6	Rent	600	120
7	Food	200	36
8	Total	800	156
9	Car	450	67.5
10	Food	100	18
11	Total	550	85.5
12	Food	150	27
13	Total	150	27
Lists

Cell Formulas
Range	Formula
C2:C13	C2	=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]

You're welcome & thanks for the feedback.