Userform, Combobox - Sum based on date and value in column

[Lukael]

Hi,

I have dates in column A, drop-down lists with values "P", "R" and "p" in column B, and values in column C.

I have created Userform with combobox and textbox, in which I would like to add new Sums to existing average of values for month. What I would like is
to see in Userform also sums for each drop-down list value of selected month.

Userform example for January:

Average:....
P:...
p:...
R:...

I hope you understand, sample is attached !! Userform can be accessed by double clicking in column C.

P.S. : Also, the existing "Average" calculation is simply reading the cell values with formula. It would be nice If someone told me how to insert this formula in VBA ?!?

Any help appreciated !

[bakerman2]

Try this one.

[Lukael]

A masterpiece, thanks bakerman2, It works great !!

[bakerman2]

Glad to help and thanks for the rep.

[Lukael]

Sorry to bother again, but I didn't quite fully test everything. It's not working with numbers over 100.000 (and I need that), I get "overflow" error, bump

Can you fix this ?

[bakerman2]

Replace top line with this

Public cP As Double, sP As Double, cR As Double

In customaverage function replace ladt line with

CUSTOMAVERAGE = Round(Total / Count, 4)

If you want other round after decimal change 4 to desired number of items after decimal.

[Lukael]

Ok, thank you. Now It really works. Can I cut a little more time from you to add some explanation of the code ? I can't fully understand it. Thanks for all your help !

[bakerman2]

Which part don't you understand because it's rather straight forward coding I used.

[Lukael]

I don't understand how customaverage function works. I need to change It a bit, in order to work. See this sample, this is exacty how columns are placed in my workbook (just one more column to right for values).

Result of average function in userfom is 131363,1, which is wrong. Result should be 193917, as in cell value in G4.

What's wrong, range ?

[bakerman2]

Because there are empty cells in your value column whe have to take this in account for calculation. (previous example had no empty values)
See revised code.

Function CUSTOMAVERAGE(rng As Range, wMonth As Integer)
    cP = 0: sP = 0: cR = 0
    For Each cell In rng
        If Month(cell) = wMonth Then
            Total = Total + cell.Offset(, 3).Value: If cell.Offset(, 3) <> vbNullString Then Count = Count + 1
            Select Case cell.Offset(, 1).Value
                Case "P"
                    cP = cP + cell.Offset(, 3).Value
                Case "p"
                    sP = sP + cell.Offset(, 3).Value
                Case "R"
                    cR = cR + cell.Offset(, 3).Value
            End Select
        End If
    Next cell
    CUSTOMAVERAGE = Round(Total / Count, 1)
End Function

[bakerman2]

Also added error handling when no values are found.

Function CUSTOMAVERAGE(rng As Range, wMonth As Integer)
    cP = 0: sP = 0: cR = 0
    For Each cell In rng
        If Month(cell) = wMonth Then
            Total = Total + cell.Offset(, 3).Value: If cell.Offset(, 3) <> vbNullString Then Count = Count + 1
            Select Case cell.Offset(, 1).Value
                Case "P"
                    cP = cP + cell.Offset(, 3).Value
                Case "p"
                    sP = sP + cell.Offset(, 3).Value
                Case "R"
                    cR = cR + cell.Offset(, 3).Value
            End Select
        End If
    Next cell
    If Total = 0 Then CUSTOMAVERAGE = 0: Exit Function
    CUSTOMAVERAGE = Round(Total / Count, 1)
End Function

[Lukael]

Thanks, can you comment this whole function line by line, I don't understand how exactly this works, everything else is reasonable to me ?

[bakerman2]

Function CUSTOMAVERAGE(rng As Range, wMonth As Integer)
    cP = 0: sP = 0: cR = 0
    For Each cell In rng 'check every filled cell in column A
        If Month(cell) = wMonth Then 'check if month equals combobox month
            'if the valuecell(column D) isn't empty add value to Total. Increment count for later calculation
            If cell.Offset(, 3) <> vbNullString Then Total = Total + cell.Offset(, 3).Value: Count = Count + 1
            Select Case cell.Offset(, 1).Value 'check dropdown column on each loop for value (column B)
                Case "P"
                    cP = cP + cell.Offset(, 3).Value
                Case "p"
                    sP = sP + cell.Offset(, 3).Value
                Case "R"
                    cR = cR + cell.Offset(, 3).Value
            End Select
        End If
    Next cell
    If Total = 0 Then CUSTOMAVERAGE = 0: Exit Function 'if no values found set result to 0 to avoid error and exit function
    CUSTOMAVERAGE = Round(Total / Count, 1) 'calculation of total values divided by number of values summed.
End Function

[Lukael]

Thanks a lot bakerman, you're the man !!