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

[mohr96]

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.

#	Properties		New Sum
1	Total Conditions	20	=?
2	New amount of conditions	5	=?
3	Number of conditions per bin	4	=?
4			=?
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

<tbody>
</tbody>

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.

#	Properties		New Sum
1	Total Conditions	20	=?
2	New amount of conditions	4	=?
3	Number of conditions per bin	5	=?
4			=?
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

<tbody>
</tbody>

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.

#	Properties		New Sum	#	Properties		New Sum
1	Total Conditions for column A	10	=sum(A2:A6)		Total Conditions for column E	10
2	New amount of conditions	2	=sum(A7:A11)		New amount of conditions	5
3	Number of conditions per bin	5			Number of conditions per bin	2	=sum(E12:E13)
4							=sum(E14:E15)
5							=sum(E16:E17)
6							=sum(E18:E19)
7							=sum(E20:E21)
8
9
10
				11
				12
				13
				14
				15
				16
				17
				18
				19
				20

<tbody>
</tbody>

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.

 

[MickG]

Perhaps this for results on last Table :-

[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

 

[mohr96]

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.

 

[MickG]

Try this code which should replace the value with an equation showing a "Sum" function.

[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