# Thread: SUM for a dynamic table

1. ## SUM for a dynamic table

Hey guys!

I'm working currently on a function for a growing table.
The current structure is following this approach:
Unbenannt.PNG

The first column consists the number for the Person; Column B the Month und C the respective Value.

The aim is to sum the months, if the cumulated value is greater than (>=) 15.
So the first person has only a cumulated value of 2 > no sum or call
The second person (16) has a cumulated value of 15 > sum (1)
So the month September get the value of +1 (in a matrix with a months and the value/ sum)
The third person has an value 15 at and 20.

The problem is that I need only the sum of the months, if the value is >=15
Then I need the sum, if the value is growing up to 20.
The steps a defined by 5 units.
So the first sum is called by 15 and the second sum by 20 and so on.
The table ist growing. So the person gets by the next update maybe a new row and the cumulated value ist growing with every update.

Do you understand this approach/ problem?
Or do you need more information?

Best regards!  Reply With Quote

2. Try this, you haven't siad where you want the results so a have output it to columns j and K

```Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
Dim Monarr(1 To 12, 1 To 2) As Variant
Monarr(1, 1) = "January"
Monarr(2, 1) = "February"
Monarr(3, 1) = "March"
Monarr(4, 1) = "April"
Monarr(5, 1) = "May"
Monarr(6, 1) = "June"
Monarr(7, 1) = "July"
Monarr(8, 1) = "August"
Monarr(9, 1) = "September"
Monarr(10, 1) = "October"
Monarr(11, 1) = "November"
Monarr(12, 1) = "December"
For k = 1 To 12
Monarr(k, 2) = 0
Next k

cnt = 0
cntlim = 15
person = 0
For i = 2 To lastrow
If inarr(i, 1) <> person Then
' new person
cnt = 0
cntlim = 15
person = inarr(i, 1)
End If

cnt = cnt + inarr(i, 3)
If cnt >= cntlim Then
cntlim = cntlim + 5
For k = 1 To 12
If inarr(i, 2) = Monarr(k, 1) Then
Monarr(k, 2) = Monarr(k, 2) + 1
End If
Next k
End If
Next i
Range(Cells(1, 10), Cells(12, 11)) = Monarr

End Sub```  Reply With Quote

3. Thanks a lot!
I thought it would maybe work with excel functions, but this works too!

Is your concept also as a function (own vba function) possible?  Reply With Quote

4. You can't have one function that writes the outputs for all months because a spreadsheet function can only write into one cell, however the code is easily modified so that you can enter the month as a parameter and it will add the sum for that month see below:

In the spreadsheet put
`=testfunction(J1)`
where J1 holds the text for the month concerned ( I used the cell that my subroutine wrote into)

then the function is:
```Function testfunction(Mnth As Range)
allcnt = 0
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))

cnt = 0
cntlim = 15
person = 0
For i = 2 To lastrow
If inarr(i, 1) <> person Then
' new person
cnt = 0
cntlim = 15
person = inarr(i, 1)
End If

cnt = cnt + inarr(i, 3)
If cnt >= cntlim Then
cntlim = cntlim + 5

If inarr(i, 2) = Mnth Then
allcnt = allcnt + 1
End If

End If
Next i
testfunction = allcnt

End Function```
If you then copy this function down you will get the other months ( assuming the text is in column j)  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•