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]

Ooh that's better and really quick, thanks for all your help with this

 

[cjcass]

Hi WBD,
I have developed my spsht further over the weekend and wanted to ask for a bit more adjustment to your coding if possible?
Originally I was just interrogating averages on one wksheet (Data) but now need to count averages on 10 wkshts (named 1,2,3..10) and deposit the average counts into 10 sets of buckets all in the Report wksht. The buckets best sitting below one another, so the results from wksht '1' would sit in $N$17:$Q$17 and wksht '2' $N$18:$Q$18 and so on. The header criteria would be the same for all. Is this achievable??
I was going to generate 10 separate copies of your existing code but thought there may be a better more efficient option using one code.
Appreciate your time if you're able to assist further.
Rgds,

 

[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 on the Report sheet
Public Sub WBD20170821()

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

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

' Process all data sheets
For dataSheet = 1 To 10
    ' 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
            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
        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

 

[cjcass]

Awesome!
Thank you.

 

[Nishant94]

Using Formula and without helper column,

Unknown
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10
3	9	5	10	5	2	2	4	9	7	4
4	3	9	10	5	6	5	3	10	5	4
5	6	7	1	4	10	9	4	8	7	4			Include Headers in the average
6	10	6	1	5	8	8	8	6	9	3			Header1
7	9	4	5	9	1	1	3	6	10	1			Header2
8	1	6	1	8	2	3	8	8	4	8			Header6
9	7	5	4	8	8	6	2	8	2	1
10	6	2	5	5	3	9	10	8	6	6
11	5	10	6	3	10	9	8	1	1	8
12	1	3	1	6	6	9	9	3	9	10
13	3	10	10	1	2	1	4	6	2	9
14	2	9	3	6	10	1	2	5	10	6
15	4	4	8	3	1	7	7	3	4	6
16	4	3	8	9	3	6	10	3	10	9
17	6	3	1	3	5	2	6	6	9	6			Averages	Result (Count)
18	6	7	6	2	5	2	1	10	9	7			6	8
19	4	8	5	8	8	1	4	6	3	6			4.5	19
20	6	6	8	5	3	7	2	7	6	6			3	28
21	6	10	4	2	1	7	2	3	1	10			3	1
22	10	9	4	4	7	5	10	5	5	7
23	1	7	2	9	2	2	1	9	10	5
24	5	9	4	7	1	6	3	10	10	3
25	10	1	5	2	1	4	5	4	7	3
26	1	3	8	7	6	1	1	7	5	9
27	3	10	1	8	8	4	2	4	10	6
28	5	2	1	7	6	8	4	9	10	10
29	5	2	10	5	3	3	3	1	2	8
30	6	5	5	9	9	2	3	4	7	7
31	8	1	10	7	3	10	5	6	5	1
Sheet6

Cell Formulas
Range	Formula
N18	{=SUM(IF((MMULT(IF(ISNUMBER(MATCH($A$2:$J$2,$M$6:$M$15,0)),($A$3:$J$3)^0,0),TRANSPOSE($A$3:$J$31))/COUNTA($M$6:$M$15))>M18,1,0))}
N21	{=SUM(IF((MMULT(IF(ISNUMBER(MATCH($A$2:$J$2,$M$6:$M$15,0)),($A$3:$J$3)^0,0),TRANSPOSE($A$3:$J$31))/COUNTA($M$6:$M$15))<=M21,1,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cjcass]

Hi WBD,
Me again but hopefully the last query on this one!
I wanted to substitute the bucket criteria with references to values in cells so I changed the following:
Case Is > 6
to
Case Is > Range ("N10")
but it won't play, what would be the correct code for this? N10 will obviously contain the number 6
Cheers.

 

[cjcass]

Th Range("N10") is on the Report wksht

 

[wideboydixon]

I guess you'll need something like this:

Case Is > wsReport.Range("N10").Value

WBD

 

[cjcass]

Hi,
it doesn't seem to like that - Run Time Error 16 - Expression too complex.

 

[wideboydixon]

OK. Then perhaps this:

            ' Decide which bucket to put the row in
            If rowAverage > wsReport.Range("N10").Value Then
                    buckets(0) = buckets(0) + 1
            ElseIf rowAverage > wsReport.Range("O10").Value Then
                    buckets(1) = buckets(1) + 1
            ElseIf rowAverage > wsReport.Range("P10").Value Then
                    buckets(2) = buckets(2) + 1
            Else
                    buckets(3) = buckets(3) + 1
            End If

WBD