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]

 

[wideboydixon]

The only way I could come up with using formulas requires a helper column:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10
2	9	5	10	5	2	2	4	9	7	4	5.33
3	3	9	10	5	6	5	3	10	5	4	5.67
4	6	7	1	4	10	9	4	8	7	4	7.33		Include Headers in the average
5	10	6	1	5	8	8	8	6	9	3	8.00		Header1
6	9	4	5	9	1	1	3	6	10	1	4.67		Header2
7	1	6	1	8	2	3	8	8	4	8	3.33		Header6
8	7	5	4	8	8	6	2	8	2	1	6.00
9	6	2	5	5	3	9	10	8	6	6	5.67
10	5	10	6	3	10	9	8	1	1	8	8.00
11	1	3	1	6	6	9	9	3	9	10	4.33
12	3	10	10	1	2	1	4	6	2	9	4.67
13	2	9	3	6	10	1	2	5	10	6	4.00
14	4	4	8	3	1	7	7	3	4	6	5.00
15	4	3	8	9	3	6	10	3	10	9	4.33
16	6	3	1	3	5	2	6	6	9	6	3.67		Averages	Result (Count)
17	6	7	6	2	5	2	1	10	9	7	5.00		>6	8
18	4	8	5	8	8	1	4	6	3	6	4.33		>4.5	19
19	6	6	8	5	3	7	2	7	6	6	6.33		>3	28
20	6	10	4	2	1	7	2	3	1	10	7.67		<=3	1
21	10	9	4	4	7	5	10	5	5	7	8.00
22	1	7	2	9	2	2	1	9	10	5	3.33
23	5	9	4	7	1	6	3	10	10	3	6.67
24	10	1	5	2	1	4	5	4	7	3	5.00
25	1	3	8	7	6	1	1	7	5	9	1.67
26	3	10	1	8	8	4	2	4	10	6	5.67
27	5	2	1	7	6	8	4	9	10	10	5.00
28	5	2	10	5	3	3	3	1	2	8	3.33
29	6	5	5	9	9	2	3	4	7	7	4.33
30	8	1	10	7	3	10	5	6	5	1	6.33
Sheet1

Cell Formulas
Range	Formula
K2	=SUMPRODUCT(($A2:$J2)*(--ISNUMBER(MATCH($A$1:$J$1,$M$5:$M$14,0))))/COUNTA($M$5:$M$14)
N17	=COUNTIFS($K$2:$K$30,">6")
N18	=COUNTIFS($K$2:$K$30,">4.5")
N19	=COUNTIFS($K$2:$K$30,">3")
N20	=COUNTIFS($K$2:$K$30,"<=3")

You can always hide column K or put it out of sight to the right ...

WBD

 

[cjcass]

Hi,
Thanks for your solution I'll keep it in reserve if I can't find one without a helper column. These tables will be appearing on 50 different worksheets and I was hoping to keep them free from formulas hence hoping for a solution without helpers.

 

[cjcass]

any other views on this?

 

[cjcass]

maybe a solution using vba if a formula without a helper column is not achievable?

 

[wideboydixon]

This will do it for the current sheet:

Public Sub WBD20170817()

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

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

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

' Work through all rows
For thisRow = 2 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(Cells(1, thisCol).Value, Range("$M$5:$M$14"), 0)) Then
            ' Yes it is - accumulate this value and add 1 to the header count
            headerCount = headerCount + 1
            rowAverage = rowAverage + 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
    buckets(0) = buckets(0) + IIf(rowAverage > 6, 1, 0)
    buckets(1) = buckets(1) + IIf(rowAverage > 4.5, 1, 0)
    buckets(2) = buckets(2) + IIf(rowAverage > 3, 1, 0)
    buckets(3) = buckets(3) + IIf(rowAverage <= 3, 1, 0)
Next thisRow

' Now populate the totals into the table
For thisRow = 0 To 3
    Cells(thisRow + 17, "N").Value = buckets(thisRow)
Next

End Sub

WBD

 

[cjcass]

Hi WBD,

Thanks for this it works great. Have a few questions re. tweaking this code if I may:

1. If my table headers drop down to Row 4 (so I can add some other info above the table) how would I change the code?
2. How would I tweak the criteria in the buckets to add an 'And', so for example bucket (1) reads if the row average is >4.5 and <6?
3. How would I change the code if I wanted the buckets to populate cells across the row (N17:Q17) as opposed to down the column (N17:N20)?
4. My criteria cells and the buckets will be located in a worksheet called 'Report' whereas the table of data will be located in a sheet called 'Data', how would this be coded?

Many thanks for all your help

 

[wideboydixon]

Private Const HEADER_ROW = 4 ' Set to be the row that contains the headers on the Data sheet
Private Const CRITERIA_RANGE = "$M$5:$M$14" ' Set to be the range that contains the criteria on the Report sheet
Private Const BUCKET_RANGE = "$N$17:$Q$17" ' Set to be the range where we will put the values
Public Sub WBD20170818()

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

' Find the sheets
Set wsReport = Worksheets("Report")
Set wsData = Worksheets("Data")

' 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
    wsData.Cells(thisRow, thisCol).Value = rowAverage
    
    ' Decide which bucket to put the row in
    If rowAverage > 6 Then buckets(0) = buckets(0) + 1
    If rowAverage > 4.5 And rowAverage <= 6 Then buckets(1) = buckets(1) + 1
    If rowAverage > 3 And rowAverage <= 4.5 Then buckets(2) = buckets(2) + 1
    If rowAverage <= 3 Then buckets(3) = buckets(3) + 1
Next thisRow

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

End Sub

I think that should answer everything. If the buckets are exclusive then you could replace this:

    ' Decide which bucket to put the row in
    If rowAverage > 6 Then buckets(0) = buckets(0) + 1
    If rowAverage > 4.5 And rowAverage <= 6 Then buckets(1) = buckets(1) + 1
    If rowAverage > 3 And rowAverage <= 4.5 Then buckets(2) = buckets(2) + 1
    If rowAverage <= 3 Then buckets(3) = buckets(3) + 1

with this:

    ' Decide which bucket to put the row in
    Select Case rowAverage
        Case Is > 6
            buckets(0) = buckets(0) + 1
        Case Is > 4.5
            buckets(1) = buckets(1) + 1
        Case Is > 3
            buckets(2) = buckets(2) + 1
        Case Else
            buckets(3) = buckets(3) + 1
    End Select

WBD

 

[cjcass]

Hi,
Thanks for this, one thing though - when I run the code after I've changed the header criteria the cells in column N on the Data sheet are populated with the averages down all the rows in the table - not sure if that's meant to happen? if it is it's a slow population of data.
Rgds,

 

[wideboydixon]

Oops. That was a line of debugging I put in. Please remove this line:

    wsData.Cells(thisRow, thisCol).Value = rowAverage

WBD