How do I sum a varying amount of rows based on a user input?

[mohr96]

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 VBA 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!

 

[Jonmo1]

Try

=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)

 

[mohr96]

It works! The only thing I had to change was the $a$2 to $a$1 for the starting point, but otherwise works like a charm. Thank you very much!

 

[Jonmo1]

You're welcome.

Why would you change A2 to A1 ?

You said the first sum should be =SUM(A2:A5)

Changing A2 to A1 makes it =SUM(A1:A4)

 

[mohr96]

For the graph that I placed in here it would work, but for my workbook that I actually pulled the data from, I needed to change the cell number.