How do I sum a varying amount of rows based on a user input for two separate columns?

M

mohr96

New Member
Joined
Jun 22, 2016
Messages
34
Hello,

I posted a similar question on here last week regarding how to sum up a varying amount of rows based on a user's input. Here is the post:

Hello,

I am trying to figure out a way to sum a varying amount of rows in excel based on a user input. I will post the images of my spreadsheet below to allow for a better visual representation. In the far left column, are values from 1 to 20. You will then notice in the near left column, the "Properties" column and another three values, just to the right of it. The second row and near right column of the table counts the total number of values that have been placed in the far left column, obviously it is currently 20 values that I have called "conditions". The third row and near right column number, is used as a user input. In this case the user wants the total number of conditions consolidated down to five total conditions. Finally, the fourth row and near right column number calculates the total number of conditions that need to be placed into "bins", a simple division calculation (total conditions/new conditions). So, depending on what the number that the fourth row and near right column contains, will be the amount of conditions that are summed together.

For both tables that I have placed below, assume the top row, far left column is cell A1. For the first table below, because the number of conditions per bin is four, I need four rows per summation. In this case, I would need sum(A2:A5), sum(A6:A9), sum(A10:A13), sum(A14:A17), and sum(A18:A21) to be performed. I have also highlighted the numbers that will correspond to each bin in different colors to allow for a better understanding.
[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]#[/TD]
[TD]Properties[/TD]
[TD][/TD]
[TD]New Sum[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total Conditions[/TD]
[TD]20[/TD]
[TD]=?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]New amount of conditions[/TD]
[TD]5[/TD]
[TD]=?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Number of conditions per bin[/TD]
[TD]4[/TD]
[TD]=?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]=?[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In the next table below, the difference is that the user now wants only four conditions, so now there will be five conditions per bin. In this case, sum(A2:A6), sum(A7:A11), sum(A12:A16), and sum(A17:A21) will need to be performed. I have highlighted the values in different colors to represent the summations visually.
[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[/TR]
[TR]
[TD]#[/TD]
[TD]Properties[/TD]
[TD][/TD]
[TD]New Sum[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total Conditions[/TD]
[TD]20[/TD]
[TD]=?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]New amount of conditions[/TD]
[TD]4[/TD]
[TD]=?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Number of conditions per bin[/TD]
[TD]5[/TD]
[TD]=?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]=?[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[/TR]
</tbody>[/TABLE]


My question is, is there a function or <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> coding out there that would allow me to perform these operations with only the user input. In other words, so that I do not have to manually perform all of those sum functions for each row. Please let me know. Thanks!

I eventually got the answer of:

=SUM(OFFSET($A$2,(ROWS($1:1)-1)*$C$4,0,$C$4,1))

A2 is the first cell to sum
C4 contains the number of cells for each sum (4 in your example)

Which proved to be correct. To expand upon this question, however, how would I be able to do the following. Again, assume the top left cell is A1.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]Properties[/TD]
[TD][/TD]
[TD]New Sum[/TD]
[TD]#[/TD]
[TD]Properties[/TD]
[TD][/TD]
[TD]New Sum[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Total Conditions for column A[/TD]
[TD]10[/TD]
[TD]=sum(A2:A6)[/TD]
[TD][/TD]
[TD]Total Conditions for column E[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]New amount of conditions[/TD]
[TD]2[/TD]
[TD]=sum(A7:A11)[/TD]
[TD][/TD]
[TD]New amount of conditions[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Number of conditions per bin[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Number of conditions per bin[/TD]
[TD]2[/TD]
[TD]=sum(E12:E13)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=sum(E14:E15)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=sum(E16:E17)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=sum(E18:E19)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=sum(E20:E21)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

To explain the above visual, I would like the numerical data in column A to be summed up the same way. In this case, the summing calculations are taking place in column D for the numerical data in column A. I would like the numerical data in column E, however, to be summed up in its respective way, in this case in column H and placed one row below the last summing calculation that is taking place in column D. In other words, in this case the last summing calculation for column A is taking place in cell D3. With this, I want the first summing calculation for column E to take place in cell H4. Is there an excel function that can be used for this operation, and/or how can I potentially adjust the previous formula that has been given to me? Please let me know. Thanks.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Perhaps this for results on last Table :-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG30Jun40
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] Variant, Rng [COLOR="Navy"]As[/COLOR] Range, Tot [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] nBin [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
rw = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] col [COLOR="Navy"]In[/COLOR] Array(1, 5)
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(2, col), Cells(Rows.Count, col)).SpecialCells(xlCellTypeConstants)
    Num = Cells(3, col + 2)
    nBin = Rng.Count / Num
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            c = c + 1
            Tot = Tot + Dn.Value
                [COLOR="Navy"]If[/COLOR] c Mod nBin = 0 [COLOR="Navy"]Then[/COLOR]
                    rw = rw + 1
                    Cells(rw, col + 3) = Tot
                    Tot = 0
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] col
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
It works fine, but when I run it, it deletes the summing calculations from column D and changes them to just straight numbers, is there any way that the equations can be protected while using this code? In other words, in the case of my example above, I performed the summing calculation and added up to 15 and 40 in cells D2 and D3 respectively. Once I ran the code, however, it kept the 15 and 40, but deleted the actual equation as to how the cells came up with those values, so I had to manually type in the equations each time. I tried to protect those cells, but then the code would not run. Please let me know. Thanks.
 
Last edited:
Upvote 0
Try this code which should replace the value with an equation showing a "Sum" function.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG01Jul46
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] Variant, Rng [COLOR="Navy"]As[/COLOR] Range, Tot [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] nBin [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range
rw = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] col [COLOR="Navy"]In[/COLOR] Array(1, 5)
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(2, col), Cells(Rows.Count, col)).SpecialCells(xlCellTypeConstants)
    Num = Cells(3, col + 2)
    nBin = Rng.Count / Num
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            c = c + 1
            [COLOR="Navy"]If[/COLOR] R [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
               [COLOR="Navy"]Set[/COLOR] R = Dn
             [COLOR="Navy"]Else[/COLOR]
               [COLOR="Navy"]Set[/COLOR] R = Union(R, Dn)
             [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]If[/COLOR] c Mod nBin = 0 [COLOR="Navy"]Then[/COLOR]
                    rw = rw + 1
                    Cells(rw, col + 3).Formula = "=sum(" & R.Address & ")"
                    [COLOR="Navy"]Set[/COLOR] R = Nothing
               [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] col
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How can I create a button to insert a new row and update related formulas?
Replies
0
Views
168
Arlochorim
A
A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
145
Abdo
A
T
  • Question Question
Add Sum total only when reaching bottom of row based on value in another column
Replies
5
Views
329
awoohaw
awoohaw
O
  • Solved
Put amounts for adjacent items based on matching column between two sheets
Replies
3
Views
76
dreid1011
dreid1011
S
  • Question Question
How do I sum without knowing the range?
Replies
1
Views
301
sGraham24
S

Forum statistics

Threads
1,223,964
Messages
6,175,659
Members
452,666
Latest member
AllexDee

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