AverageIF function

[Klartigue]

Hello,

I am trying to write a code that calculates the average price based on specific criteria. The lines will always change so it needs to be a general code that says for all the same account in column A, if the security in column C is the same, then average the prices listed in column K and place that number in column O.

Attached is a example of the sheet I am working with and how I want to average the price.

[Bob Phillips]

In O6, use

=AVERAGEIFS($K$1:$K$28,$A$1:$A$28,$A6,$B$1:$B$28,$B6,$C$1:$C$28,$C6)

[Klartigue]

That works, I placed the formula in this code and it seems to be generic enough to work with all different output each time.

Sub TestAverageif()
Dim Lastrow As Long
    Dim i As Long
    With ActiveSheet
         
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To Lastrow
    With .Cells(i, "O").Select
    ActiveCell.FormulaR1C1 = "=AVERAGEIFS(R[-5]C[-4]:R[21]C[-4],R[-5]C[-14]:R[21]C[-14],R[-1]C[-14],R[-5]C[-13]:R[21]C[-13],R[-1]C[-13],R[-5]C[-12]:R[21]C[-12],R[-1]C[-12])"
    
    End With
    
Next i
End With
End Sub

[Klartigue]

Actually this doesn't seem to be working. In cell O1 the formula is =AVERAGEIFS(K23:K1048573,A23:A1048573,A1,B23:B1048573,B1,C23:C1048573,C1). It should always averaging K2 down to lastrow in K if the values in A are the same and the values in C are the same. This keeps shifting down starting at K23 which is not correct

[Klartigue]

I think something like this will work:

Sub TestAverageif()
    Dim Lastrow As Long
    Dim i As Long
    With ActiveSheet
         
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To Lastrow
            With .Cells(i, "O").Select
                ActiveCell.FormulaR1C1 = "=AVERAGEIFS(R2C11:R1048576C11,R2C1:R1048576C1,R2C1:R1048576C1,R2C3:R1048576C3,R2C3:R1048576C3)"
                 
            End With
             
        Next i
    End With
End Sub

[Bob Phillips]

Just use

Sub TestAverageif() 
    Dim Lastrow As Long

    With ActiveSheet 
         
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 
        .Range("O2").Resize(Lastrow-1).Formula = "=AVERAGEIFS($K$2:$K$" & Lastrow & ",$A$2:$A$" & Lastrow & ",$A2,$B$2:$B$" & Lastrow & ",$B2,$C$2:$C$" & Lastrow & ",$C6)"
    End With 
End Sub