SUM - N Rows above (Value of N comes from two columns one row above)

sm789

New Member
Joined
Aug 17, 2014
Messages
19
Office Version
  1. 2011
Platform
  1. MacOS
Dear All,

I am stuck and need a little help. Seems like a simple problem.

I have a list. Every day we get sales data from different locations. Each location gets one row. Then data goes in the subsequent columns. The number of rows is dynamic depending on how many locations reported for that day.

Here is the structure:

Column A has date, Column B has serial numbers of data points for that date, Column C has location, Column D through K has data for that location.
For each date, the row after the last location has totals. Here is the issue. Since the number of rows to total come from Column B (Column B is reset at 1 for each date), how do I create a SUM formula to add n number of rows when n is a value in one row above and two columns to the left?

Thank you
Sam
Screen Shot 2020-10-19 at 12.44.46 PM.png
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
Would something like this work for you?

Put the formula in E7, copy across, then copy that row of formulas to the other TOTAL rows.

20 10 19.xlsm
ABCDEFG
1
212/10/2020416
312/10/2020382
412/10/2020692
512/10/2020838
612/10/2020872
712/10/2020TOTAL292820
813/10/2020773
913/10/2020625
1013/10/2020TOTAL1398
1114/10/2020696
1214/10/2020161
1314/10/2020592
1414/10/2020227
1514/10/2020934
1614/10/2020295
1714/10/2020283
1814/10/2020TOTAL274628
1915/10/2020926
2015/10/2020125
2115/10/2020856
2215/10/2020888
2315/10/2020413
2415/10/2020653
2515/10/2020547
2615/10/2020326
2715/10/2020453
2815/10/2020292
2915/10/2020418
3015/10/2020TOTAL544457
Sum Variable rows
Cell Formulas
RangeFormula
E7:G7,E30:G30,E18:G18,E10:G10E7=SUMIF($A$2:$A6,$A7,E$2:E6)
 
Solution

sm789

New Member
Joined
Aug 17, 2014
Messages
19
Office Version
  1. 2011
Platform
  1. MacOS
Would something like this work for you?

Put the formula in E7, copy across, then copy that row of formulas to the other TOTAL rows.

20 10 19.xlsm
ABCDEFG
1
212/10/2020416
312/10/2020382
412/10/2020692
512/10/2020838
612/10/2020872
712/10/2020TOTAL292820
813/10/2020773
913/10/2020625
1013/10/2020TOTAL1398
1114/10/2020696
1214/10/2020161
1314/10/2020592
1414/10/2020227
1514/10/2020934
1614/10/2020295
1714/10/2020283
1814/10/2020TOTAL274628
1915/10/2020926
2015/10/2020125
2115/10/2020856
2215/10/2020888
2315/10/2020413
2415/10/2020653
2515/10/2020547
2615/10/2020326
2715/10/2020453
2815/10/2020292
2915/10/2020418
3015/10/2020TOTAL544457
Sum Variable rows
Cell Formulas
RangeFormula
E7:G7,E30:G30,E18:G18,E10:G10E7=SUMIF($A$2:$A6,$A7,E$2:E6)
Thank you Sir! This requires us to modify the Total formula each time depending on the number of rows above. I was hoping for a formula that can be copied without having to modify it. Basically I was wondering if it is possible to do something like SUM(from cell above to n number of rows; where n is from Column B of the row above). Thanks
 

sm789

New Member
Joined
Aug 17, 2014
Messages
19
Office Version
  1. 2011
Platform
  1. MacOS
Sorry! I spoke too soon. This works for me. Simple and beautiful!

Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome.

You do have to adjust the first formula only each day to account for the number of rows in that first section, that is all.
The other option is to have a small macro to insert the formulas for you.

Example.
Before the macro

20 10 19.xlsm
ABCDEFGHIJK
112/10/202014162922
212/10/202023828339
312/10/202036924229
412/10/202048388327
512/10/202058726829
612/10/2020TOTAL
713/10/202017732929
813/10/202026252281
913/10/2020TOTAL
1014/10/202016967694
1114/10/202021616999
1214/10/202035926971
1314/10/202042277745
1414/10/202059342581
1514/10/202062953696
1614/10/202072833467
1714/10/2020TOTAL
1815/10/202019263411
1915/10/202021255756
2015/10/202038562672
2115/10/202048889343
2215/10/202054131195
2315/10/202066533489
2415/10/202075476182
2515/10/202083266315
2615/10/202094534342
2715/10/2020102925328
2815/10/2020114183844
2915/10/2020TOTAL
Sum Variable rows



Macro:
VBA Code:
Sub InsertTotalFormulas()
  Intersect(Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).EntireRow, Columns("E:K")).FormulaR1C1 = "=SUMIF(R1C1:R[-1]C1,RC1,R1C:R[-1]C)"
End Sub


After the macro:

20 10 19.xlsm
ABCDEFGHIJK
112/10/202014162922
212/10/202023828339
312/10/202036924229
412/10/202048388327
512/10/202058726829
612/10/2020TOTAL29282028251136
713/10/202017732929
813/10/202026252281
913/10/2020TOTAL13984111010
1014/10/202016967694
1114/10/202021616999
1214/10/202035926971
1314/10/202042277745
1414/10/202059342581
1514/10/202062953696
1614/10/202072833467
1714/10/2020TOTAL27462834465233
1815/10/202019263411
1915/10/202021255756
2015/10/202038562672
2115/10/202048889343
2215/10/202054131195
2315/10/202066533489
2415/10/202075476182
2515/10/202083266315
2615/10/202094534342
2715/10/2020102925328
2815/10/2020114183844
2915/10/2020TOTAL54445747435347
Sum Variable rows
Cell Formulas
RangeFormula
E6:K6,E29:K29,E17:K17,E9:K9E6=SUMIF($A$1:$A5,$A6,E$1:E5)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
BTW, I suggest that you ..
  • update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
  • investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
 

sm789

New Member
Joined
Aug 17, 2014
Messages
19
Office Version
  1. 2011
Platform
  1. MacOS
Thank you very much! The formula works very well. I will try the vba macro as well simply because I love to play around with macros and learn new things.

Have updated my profile! Thank you again!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,750
Messages
5,574,016
Members
412,563
Latest member
marianmalone2019
Top