Total of column since the previous total

Herostrata

New Member
Joined
May 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.:

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

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
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=IF(A2="Total",SUM(B$2:B2)-SUM(C$1:C1),"")
 

Herostrata

New Member
Joined
May 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

1621250821365.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+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)))
 
Solution

Herostrata

New Member
Joined
May 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+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
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

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