SUM for a dynamic table

[joshua1990]

Hey guys!

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

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!

[offthelip]

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

[joshua1990]

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?

[offthelip]

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)