VBA for total and subtotal

anzer

Board Regular
Joined
Mar 5, 2015
Messages
65
Hello,

I have a spreadsheet with column I where there is a lot of data.
I have the 1000 which is the sum of the 2x500 (bold)
The 2x500 are the sum of the data (first one is 200 =300 and second one is sum of 5x100)

I would need a VBA that would populate the formulas taking in consideration the end of the selections
Column I is going down to about I1000 and even more so doing this manually is long and time consuming

Is there a way to do this with VBA? As I have several columns like "I".

Thank you very much

1000sum of the 2 x500
500sum of the 200 and 300 below
200
300
500sum of the 5x100 below
100
100
100
100
100
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
508
Office Version
365
Platform
Windows
Are you trying to do a parent child calculation? Bill of Materials Cost Rollup?
 

anzer

Board Regular
Joined
Mar 5, 2015
Messages
65
It is liek costs rate total. The 100,200 and 300 would be a formulas to calculate the time and rate of a task. The 500 would be the sum of the tasks. And the 1000 would be the sum of the series of tasks.
 

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
508
Office Version
365
Platform
Windows
Column C Formula
=IF(A2=2,SUM(OFFSET(INDIRECT(ADDRESS(ROW(),2)),0,0,IFERROR(MATCH(2,A3:A12,0),1+COUNTA(A3:A12)))),0)

Column D Formula
=IF(A2=1,SUM(OFFSET(INDIRECT(ADDRESS(ROW(),3)),0,0,IFERROR(MATCH(1,A3:A12,0),1+COUNTA(A3:A12)))),0)

Column E Formula
=SUM(B2:D2)


Snag_92cd91d.png


Snag_92d0a3f.png
 

anzer

Board Regular
Joined
Mar 5, 2015
Messages
65
Hello VBE! I thought of something like that too! but I would have needed the formulas, but I am still taking your method as of now :) Thank you for your time!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,005
Messages
5,465,959
Members
406,456
Latest member
jmishra91

This Week's Hot Topics

Top