Sum up column totals after user inputs number of rows

jgastel

New Member
Joined
Mar 10, 2015
Messages
5
I am trying to convert an old paper form to Excel and am a novice at best with VBA. I am wondering if anyone can help me figure out the code to automatically sum up the total of the various columns in the below spreadsheet. The user inputs the number of rows needed when they open the work book but I can't seem to figure out how to get the various columns to subtotal in row 10 based on the number of rows they input. Any help/advice would be greatly appreciated! Thank you. JG

1588707373162.png
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Let's say cell A7 contains the number of rows you want to sum. In cell D10: =sum(offset(D$8,1,0,$A$7,1)

No VBA, just a formula
 
Upvote 0
Jeffrey,
Thanks for the reply. I apologize but my narrative above wasn't clear. So when a user opens the workbook, it is going to ask them to input a number of rows. Lets say they pick 5. The result of is rows 10-14 are added below row 9 and row 15 becomes the TOTALS row at the bottom. I need to be able to sum up the totals in each column based on the number of rows selected by the user. In my example here, in column E, I need to sum E9 through E14 and that total would go in E15. Same for columns G, I, J, K, L, O, P, Q, R, S. I appreciate your help! J
 
Upvote 0
The formula I gave you can be altered to do what you're asking. Read about the offset function
 
Upvote 0
Thanks Jeffrey,
I dabbled with it the other night after your initial reply but couldn't quite figure it out. I'll look into it more today. I appreciate the help!
 
Upvote 0
In cell D15: =sum(offset(D$10 ,0 ,0 ,$A$7-1 ,1 ))
=sum(offset(Beginning cell ,Row Offset ,Column offset ,Number of rows ,Number of Columns))

For the number or rows, I put the address for A7 anchored less one because it also includes rows 10. The user puts in 5 in cell A7 and then this formula will include A10:A14. The user alters the number or rows in the formula by changing the value in Cell A7.
 
Upvote 0
Thanks for your help Jeffrey! I was able to figure it out and learned a whole bunch of new stuff that I didn't know so it all good. Stay Healthy! JG
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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