CODEWITHSUNDEEP
excelvba
Jakob
Jakob

Reputation: 4854

creating AVERAGEIFS in Excel

I've never used AVERGEIFS before, and it's getting a little too complex for me, so please help me out.

I have a range, where I want to average column values in column A for rows that have column value B equal to 2 and column A values lesser than 3, so is this correct:

    WorksheetFunction.averageifs(columnA, columnA, "<3", columnB, "2")

EDIT I get a "Type Mismatch"

ColumnA and ColumnB are ranges, in if sentences this is what I want

IF columnA < 3 AND columnB = 2 Then Average(columnA)

Edit

I'm assuming this must have something to do with my types then, the averageifs throws a 1004 cannot get the averageifs property of class worksheetfunction when I enter my parameters, however countifs throws 13 Type Mismatch given the same parameters:

For Each column In importsheet.UsedRange.Columns
    colcount = .CountIfs(column, column, "<3", importsheet.UsedRange.Columns(DepColumn), sec)
Next

DepColumn and colcount are integers, sec is a string with an integer ("2") clusterfuck of errors above, stroke it out

Upvotes: 0

Views: 8397

Answers (2)

chris neilsen
chris neilsen

Reputation: 53166

The syntax of your posted code is correct provided

  • you have Dim'ed columnA and columnB as Range's and Set then to valid ranges
  • the data on your sheet returns a result (ie there is at least one row that meets the criteria)

Demo with error handling:

Sub Demo()
    Dim columnA As Range
    Dim columnB As Range
    Dim v
    Set columnA = [A:A]
    Set columnB = [B:B]

    On Error Resume Next
    v = WorksheetFunction.AverageIfs(columnA, columnA, "<3", columnB, "2")
    If Err.Number = 1004 Then
        'No matching data
        v = CVErr(xlErrNA)
    End If
    On Error GoTo 0

End Sub

Upvotes: 2

Peter Albert
Peter Albert

Reputation: 17505

Assuming that columnA and columnB are ranges of the same size, your formula is correct:

WorksheetFunction.Averageifs(Range("A:A"),Range("A:A"),"<3",Range("B:B"),"2")

will return you the average of column A where A<3 and B=2.

Upvotes: 2

Related Questions