Formula using averageifs & countifs with multiple criteria

[cjcass]

Hi,

I’m looking for a solution that can count the number of times averages occur in a table based on several criteria. Looking at the picture below, I’m trying to work out a formula that can go into cells N17:N20 that will calculate the count of averages that meet the variable criteria in the yellow cells (M5:M14 & M17:M20). I have highlighted the results that the formula would give for averages >6 and <=3 so you can visualise the solution.

Points to note are:
1. The number of rows in the table will be 50,000
2. The signs (“<” etc.) don’t need to be typed next to the numbers as displayed in M17:M20
3. I don’t want to use any helper columns in the solution

Hope you may be able to help, many thanks.

[/IMG]

 

[cjcass]

Brilliant, that's it sorted, thanks for all your time WBD

 

[cjcass]

Hi Nishant,
Thank you for your solution, it worked fine when all on one worksheet, but interestingly when I have the formula and the criteria cells in a different wksheet to the data table it didn't want to play. Does this formula all have to be on the same wksheet?
Rgds,

 

[cjcass]

Hi WBD,
I've adapted the code you gave me and have been using it with great success. I wondered if you'd be able to assist in adding an extra bit of criteria for me?
Currently the range C15:C26 in my wksht 'Levels' determines which headers to use in the average calculation, but additional to this I would also like to use cell C30 to detail another header to use but this header has some criteria of it's own relating to the entries in it's column down the table. The criteria for the header detailed in C30 would be written below in cells C31:C36.
So, as an example, if I typed '29' in cell C30, and '3' into C31 and '5' into C32, my code would only count the averages for the rows under the headers I have selected in C15:C26 that have 3 and 5 as cell entries under header 29. If there is no entry in cell C30 then it should be ignored.
Hope this makes sense - any help much appreciated, my existing code is below...

Private Const HEADER_ROW = 3 ' Set to be the row that contains the headers on the Data sheet
Private Const CRITERIA_RANGE = "$C$15:$C$26" ' Set to be the range that contains the criteria on the Report sheet
Private Const BUCKET_RANGE = "$I$15:$L$15" ' Set to be the range where we will put the values on the Report sheet
Public Sub Levels()


Dim lastRow As Long
Dim lastCol As Long
Dim thisRow As Long
Dim thisCol As Long
Dim buckets(3) As Long
Dim headerCount As Long
Dim rowAverage As Double
Dim wsReport As Worksheet
Dim wsData As Worksheet
Dim dataSheet As Long


'Clear previous results
Range("I15:L64").Select
    Selection.ClearContents
    Range("I15").Select


' Find the report sheet
Set wsReport = Worksheets("Levels")


' Process all data sheets
For dataSheet = 1 To 50
    ' Find the data sheet
    Set wsData = Nothing
    On Error Resume Next
    Set wsData = Worksheets(CStr(dataSheet))
    On Error GoTo 0
    
    ' Did we find the sheet
    If Not wsData Is Nothing Then
        ' Clear out the buckets
        For thisCol = 0 To 3
            buckets(thisCol) = 0
        Next thisCol
        
        ' Find the last row of data
        lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
        
        ' Find the last column
        lastCol = wsData.Cells(HEADER_ROW, wsData.Columns.Count).End(xlToLeft).Column
        
        ' Work through all rows
        For thisRow = HEADER_ROW + 1 To lastRow
            ' Reset the average for this row and the number of headers matched
            rowAverage = 0
            headerCount = 0
            
            ' Process all columns on this row
            For thisCol = 1 To lastCol
                ' Is the header on this column in the list of headers?
                If Not IsError(Application.Match(wsData.Cells(HEADER_ROW, thisCol).Value, wsReport.Range(CRITERIA_RANGE), 0)) Then
                    ' Yes it is - accumulate this value and add 1 to the header count
                    headerCount = headerCount + 1
                    rowAverage = rowAverage + wsData.Cells(thisRow, thisCol).Value
                End If
            Next thisCol
            
            ' Divide the total by the number of headers
            rowAverage = rowAverage / headerCount
            
            ' Decide which bucket to put the row in
            If rowAverage >= wsReport.Range("N6").Value Then
                    buckets(0) = buckets(0) + 1
            ElseIf rowAverage >= wsReport.Range("N7").Value Then
                    buckets(1) = buckets(1) + 1
            ElseIf rowAverage > wsReport.Range("N8").Value Then
                    buckets(2) = buckets(2) + 1
            Else
                    buckets(3) = buckets(3) + 1
            End If
            Next thisRow
            
                    
        ' Now populate the totals into the table
        For thisCol = 0 To 3
            wsReport.Range(BUCKET_RANGE).Offset(dataSheet - 1)(thisCol + 1).Value = buckets(thisCol)
        Next
    End If
Next dataSheet
End Sub

 

[wideboydixon]

I'm not sure I totally understand your requirements. Can you perhaps provide some sample data and expected results?

Thanks,

WBD

 

[cjcass]

Hi,
In the existing code my header criteria was isolated to cells C15:C26 and if I entered 1,2,3,4&5 in those cells it would have counted and bucketed the averages for all rows below those headers, but I would like to add another chunk of criteria that selects an additional header and only selects rows that meets the new header’s criteria as well as the other criteria (ie. headers 1,2,3,4&5), so with reference to the criteria detail in my last message the pic below shows which rows would be averaged (yellow) which meet both the original and the new criteria (orange).
Hope this makes sense?
Rgds,

 

[wideboydixon]

So if the row doesn't contain the right criteria under Header 29, what is the average for that row? Should it be zero and placed in the last bucket or should the row be excluded completely and not put in any bucket?

WBD

 

[cjcass]

It should be excluded completely

 

[wideboydixon]

And does the extra criteria value form part of the average or not? Is the average of row 4 in your example (4+5+4+6+2)/5 or (4+5+4+6+2+3)/6?

 

[cjcass]

No, it doesn't form part of the average so it would be (4+5+4+6+2)/5

 

[wideboydixon]

Well, you can decide by adjusting the INCLUDE_EXTRA_CRITERIA_IN_AVERAGE constant value.

Private Const HEADER_ROW = 3 ' Set to be the row that contains the headers on the Data sheet
Private Const CRITERIA_RANGE = "$C$15:$C$26" ' Set to be the range that contains the criteria on the Report sheet
Private Const BUCKET_RANGE = "$I$15:$L$15" ' Set to be the range where we will put the values on the Report sheet
Private Const BUCKET_VALUES = "$N$6:$N$8" ' Set to be the range that contains the bucket value limits
Private Const EXTRA_CRITERIA_VALUE = "$C$30" ' Set to be the extra criteria header name
Private Const EXTRA_CRITERIA_RANGE = "$C$31:$C$36" ' Set to be the extra criteria range
Private Const INCLUDE_EXTRA_CRITERIA_IN_AVERAGE = False ' Determines whether the extra criteria column is used in the average calculation
Public Sub Levels()

Dim lastRow As Long
Dim lastCol As Long
Dim thisRow As Long
Dim thisCol As Long
Dim buckets(3) As Long
Dim headerCount As Long
Dim rowAverage As Double
Dim wsReport As Worksheet
Dim wsData As Worksheet
Dim dataSheet As Long
Dim validRow As Boolean

' Find the report sheet
Set wsReport = Worksheets("Levels")

' Clear previous results from the report sheet
wsReport.Range("I15:L64").ClearContents

' Process all data sheets
For dataSheet = 1 To 50
    ' Find the data sheet
    Set wsData = Nothing
    On Error Resume Next
    Set wsData = Worksheets(CStr(dataSheet))
    On Error GoTo 0

    ' Did we find the sheet
    If Not wsData Is Nothing Then
        ' Clear out the buckets
        For thisCol = 0 To 3
            buckets(thisCol) = 0
        Next thisCol

        ' Find the last row of data
        lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row

        ' Find the last column
        lastCol = wsData.Cells(HEADER_ROW, wsData.Columns.Count).End(xlToLeft).Column

        ' Work through all rows
        For thisRow = HEADER_ROW + 1 To lastRow
            ' Reset the average for this row and the number of headers matched
            rowAverage = 0
            headerCount = 0
            
            ' Decide if this row is valid
            validRow = True

            ' Process all columns on this row
            For thisCol = 1 To lastCol
                ' Is the header on this column in the list of headers?
                If Not IsError(Application.Match(wsData.Cells(HEADER_ROW, thisCol).Value, wsReport.Range(CRITERIA_RANGE), 0)) Then
                    ' Yes it is - accumulate this value and add 1 to the header count
                    headerCount = headerCount + 1
                    rowAverage = rowAverage + wsData.Cells(thisRow, thisCol).Value
                End If
                
                ' Does this match the extra criteria?
                If wsReport.Range(EXTRA_CRITERIA_VALUE).Value <> "" Then
                    If wsData.Cells(HEADER_ROW, thisCol).Value = wsReport.Range(EXTRA_CRITERIA_VALUE).Value Then
                        If IsError(Application.Match(wsData.Cells(thisRow, thisCol).Value, wsReport.Range(EXTRA_CRITERIA_RANGE), 0)) Then
                            validRow = False
                        ElseIf INCLUDE_EXTRA_CRITERIA_IN_AVERAGE Then
                            headerCount = headerCount + 1
                            rowAverage = rowAverage + wsData.Cells(thisRow, thisCol).Value
                        End If
                    End If
                End If
            Next thisCol

            If validRow Then
                ' Divide the total by the number of headers
                rowAverage = rowAverage / headerCount
                
                ' Decide which bucket to put the row in
                If rowAverage >= wsReport.Range(BUCKET_VALUES)(1).Value Then
                        buckets(0) = buckets(0) + 1
                ElseIf rowAverage >= wsReport.Range(BUCKET_VALUES)(2).Value Then
                        buckets(1) = buckets(1) + 1
                ElseIf rowAverage > wsReport.Range(BUCKET_VALUES)(3).Value Then
                        buckets(2) = buckets(2) + 1
                Else
                        buckets(3) = buckets(3) + 1
                End If
            End If
        Next thisRow

        ' Now populate the totals into the table
        For thisCol = 0 To 3
            wsReport.Range(BUCKET_RANGE).Offset(dataSheet - 1)(thisCol + 1).Value = buckets(thisCol)
        Next
    End If
Next dataSheet

End Sub

WBD