Average across multiple sheets with a condition

Question

My Excel Workbook has several sheets. Each sheet has in cell B1 a product name.

Also, each sheet has in cells B4 to B100 lots of numbers.

I would like to get the average over all sheets. Here is what I have so far:

=AVERAGE(Sheet1:Sheet20!B4:B100)

Now this works fine, however, I would now also like to have an extra condition: Instead of getting the average over all sheets, I only want to get the average over those sheets which meet a certain product name (cell B1). If the product name is different from the specified one, the whole sheet should not be taken into consideration for the average.

Is there any simple way of doing this?

Are there any blanks in the cells (B4 thru B100) in any of the sheets?? — Gary's Student
well, in reality I am making it B4 to B10000, because I never know how many there are (but it will surely not exceed 10000). So there are blanks after the last value, but not inbetween — Christian

Gary's Student Gary's Student · Accepted Answer · 2014-03-22T17:20:52

This performs the average if the B1 contains Apfel :

Sub AboveAverage()
    Dim zum As Double, kount As Long
    Dim i As Long, r As Range, tAv As Double
    zum = 0
    kount = 0
    For i = 1 To 20
        With Sheets("Sheet" & i)
        If .Range("B1") = "Apfel" Then
            For j = 4 To 10000
                v = .Cells(j, "B").Value
                If v <> "" Then
                    zum = zum + v
                    kount = kount + 1
                End If
            Next j
        End If
        End With
    Next i
    tAv = zum / kount
    MsgBox tAv
End Sub

Average across multiple sheets with a condition

1 Answers