Summing from the beginning of table at each increment

O

OliverEx

New Member
Joined
Feb 27, 2019
Messages
2
Hi All, I'm trying to work out how to sum from the start of a table every time a number increments. Eg in the first table you would have the patten everyone gets paid in.

[TABLE="class: outer_border, width: 1852"]
<colgroup><col><col span="20"></colgroup><tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y1 / Q1[/TD]
[TD]Y1 / Q2[/TD]
[TD]Y1 / Q3[/TD]
[TD]Y1 / Q4[/TD]
[TD]Y2 / Q1[/TD]
[TD]Y2 / Q2[/TD]
[TD]Y2 / Q3[/TD]
[TD]Y2 / Q4[/TD]
[TD]Y3 / Q1[/TD]
[TD]Y3 / Q2[/TD]
[TD]Y3 / Q3[/TD]
[TD]Y3 / Q4[/TD]
[TD]Y4 / Q1[/TD]
[TD]Y4 / Q2[/TD]
[TD]Y4 / Q3[/TD]
[TD]Y4 / Q4[/TD]
[TD]Y5 / Q1[/TD]
[TD]Y5 / Q2[/TD]
[TD]Y5 / Q3[/TD]
[TD]Y5 / Q4[/TD]
[/TR]
[TR]
[TD]Person Type A[/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,400.00[/TD]
[TD="align: right"]£1,400.00[/TD]
[TD="align: right"]£1,400.00[/TD]
[TD="align: right"]£1,400.00[/TD]
[/TR]
</tbody>[/TABLE]


In the second table, I list the number of people (going up over time). Each time someone is added, their salary needs to be added to the total but starting at the beginning of the table above. I've shown below how that would be done manually.

[TABLE="class: grid, width: 1852"]
<colgroup><col><col span="20"></colgroup><tbody>[TR]
[TD]Num of Type A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,400.00[/TD]
[TD="align: right"]£1,400.00[/TD]
[TD="align: right"]£1,400.00[/TD]
[TD="align: right"]£1,400.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[TD="align: right"]£1,300.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[TD="align: right"]£1,200.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[TD="align: right"]£1,100.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]£10,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[TD="align: right"]£1,000.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]11100[/TD]
[TD="align: right"]2100[/TD]
[TD="align: right"]2100[/TD]
[TD="align: right"]2100[/TD]
[TD="align: right"]12300[/TD]
[TD="align: right"]3300[/TD]
[TD="align: right"]3300[/TD]
[TD="align: right"]3300[/TD]
[TD="align: right"]33600[/TD]
[TD="align: right"]6600[/TD]
[TD="align: right"]6600[/TD]
[TD="align: right"]6600[/TD]
[TD="align: right"]47200[/TD]
[TD="align: right"]11200[/TD]
[TD="align: right"]11200[/TD]
[TD="align: right"]11200[/TD]
[/TR]
</tbody>[/TABLE]

I can't work out how to convert that into a formula so it doesn't have to be done manually. Any pointers would be much appreciated.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
I don't understand what you're asking. Is your formula going in table 1 or 2. What cell would contain the formula?
 
Upvote 0
ValuationMan said:
I don't understand what you're asking. Is your formula going in table 1 or 2. What cell would contain the formula?
Click to expand...

Sorry, the formula would be to replace the last row of the second table. Currently, that is a sum of the row's above, which i don't want to have to generate manually.
 
Upvote 0
No problem. I have another question.

Let's take Table 2 and the second column. Let's say the bottom formula is =SUM(B3:B12). Is your issue everytime you add a row, that formula remains B3:B12 as opposed to B3:B13?

If that's the case, instead of =SUM(B3:B12), you could use =SUM(B$3:OFFSET(B13,-1,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Calendarize Sales Data stated in years from date
Replies
3
Views
511
nterhaar89
N
O
  • Solved
data rearrangement
Replies
3
Views
406
Fluff
Fluff
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
2K
DanteAmor
DanteAmor
MrDB4Excel
  • Question Question
XLOOKUP Issue
Replies
8
Views
731
MrDB4Excel
MrDB4Excel
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
710
MrDB4Excel
MrDB4Excel

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top