count occurrences across multiple sheets

[roninn75]

hi
i wish to sum / count total occurences across multiple sheets into a summary sheet.
i am attaching a sample workbook, the sheets will run for each day of the month(1:31) on the summary sheet the formula / vba must run through the sheets and count. I have explained on the summary sheet which values are to be counted.

your assistance is highly appreciated.

[HansV]

I created the following custom function to compute conditional counts over a range of sheets:

Code: Select all

Function CountIfs3D(SheetList As String, ParamArray RangesAndConditions()) As Variant
    Dim Sheet1 As Long
    Dim Sheet2 As Long
    Dim sTestRange As String
    Dim n As Long
    Dim Count As Long
    Dim p As Long
    Dim FirstSheet As Long
    Dim LastSheet As Long
    Dim wbk As Workbook
    Dim i As Long

    Application.Volatile
    Set wbk = Application.Caller.Parent.Parent
    Count = 0
    p = InStr(SheetList, ":")
    Sheet2 = Trim(Mid(SheetList, p + 1))
    If p = 0 Then
        Sheet1 = Sheet2
    Else
        Sheet1 = Trim(Left(SheetList, p - 1))
    End If
    FirstSheet = wbk.Worksheets(CStr(Sheet1)).Index
    LastSheet = wbk.Worksheets(CStr(Sheet2)).Index

    For n = FirstSheet To LastSheet
        With wbk.Worksheets(n)
            If UBound(RangesAndConditions) = 1 Then
                Count = Count + Application.WorksheetFunction.CountIfs _
                    (.Range(RangesAndConditions(0)), RangesAndConditions(1))
            Else
                Count = Count + Application.WorksheetFunction.CountIfs _
                    (.Range(RangesAndConditions(0)), RangesAndConditions(1), _
                     .Range(RangesAndConditions(2)), RangesAndConditions(3))
            End If
        End With
    Next n
    CountIfs3D = Count
End Function

I started with the code for CountIf3D on this page. I extended it to support up to two conditions. To keep things simple, I left out the error checking.

The formula in C4 is =CountIfs3D("1:5", "E1:E100",A3)
The formula in E4 is =CountIfs3D("1:5", "E1:E100",A3,"H1:H100","Adult")
The rest is similar.

The entire block of cells A4:K12 can be copied and pasted to A15, A26, etc.

See the attached version. It is now a .xlsm workbook since it contains VBA code. You will have to enable macros.

sample.xlsm

[roninn75]

thank you Hans. this works well :)