Unduplicated countif across multiple sheets

[treyjenkins08]

Hello,

I am creating a workbook to track attendance. The goal is to get an unduplicated count of "total number of individuals attended in one month" and also "total number of times service type used"

The workbook is set up like this: sheet 1 is "month ending" ; followed by 31 sheets for each day of the month (labeled 1st, 2nd, 3rd...30th & 31st) ; there is a final sheet that has a roster of enrolled persons. The roster is used to create a drop down list under the column "name"

I can get an unduplicated count for each sheet, but I do not know how to get an unduplicated count total for all sheets. Using the sumproduct and countif functions how can get an unduplicated total of individuals served for the entire month?

I made an attempt and got stuck...so I am here today. Thank you in advance.

This is the function I tried [=SUMPRODUCT(COUNTIF("'1st'!A5:A154!";"'2nd'!A5:A154";"'3rd'!A5:A154";"'4th'!A5:A154";"'5th'!A5:A154";"'6th'!A5:A154";"'7th'!A5:A154";"'8th'!A5:A154";
"'9th'!A5:A154";"'10th'!A5:A154";"'11th'!A5:A154";"'12th'!A5:A154";"'13th'!A5:A154";"'14th'!A5:A154";"'15th'!A5:A154";"'16th'!A5:A154";
"'17th'!A5:A154";"'18th'!A5:A154";"'19th'!A5:A154";"'20th'!A5:A154";"'21st'!A5:A154";"'22nd'!A5:A154";"'23rd'!A5:A154";"'24th'!A5:A154";
"'25th'!A5:A154";"'26th'!A5:A154";"'27th'!A5:A154";"'28th'!A5:A154";"'29th'!A5:A154";"'30th'!A5:A154";"'31st'!A5:A154" & A5:A154))]

[Bernie Deitrick]

My standard reply is "Don't do that!" Use one sheet, with a column for date. Then you can use pivot tables, filters, normal formulas etc.

If you are determined to continue with multple sheets (as you described your workbook structure), you could use a User-Defined-Function (a macro). Copy the code below into a standard codemodule, set a reference to MS scripting Runtime, and use the function like so:

=UniqueCount(A5:A154)

If you want a list of all the unique values, run the macro "GetUniques" When prompted, select A5:A154 on any sheet, and an anchor cell for output.

Function UniqueCount(rMN As Range) As Integer
    Dim Sh As Worksheet
    Dim Dict As Scripting.Dictionary
    Dim i As Long
    
    Set Dict = New Scripting.Dictionary
    
    With Dict
        For Each Sh In Worksheets
        For i = 1 To Sh.Range(rMN.Address).Cells.Count
            If Sh.Range(rMN.Address).Cells(i).Value <> "" Then
                .Item(Sh.Range(rMN.Address).Cells(i).Value) = 1
            End If
        Next i
        Next Sh
        UniqueCount = .Count
    End With
    
End Function

Sub GetUniques()
    Dim rngV As Range
    Dim rngO As Range
    Set rngV = Application.InputBox("Select the range to check (on one sheet, not all)", Type:=8)
    Set rngO = Application.InputBox("Select the anchor cell for output.", Type:=8)
    UniqueValues rngV, rngO
End Sub

Sub UniqueValues(rMN As Range, rOut As Range)
    Dim Sh As Worksheet
    Dim Dict As Scripting.Dictionary
    Dim i As Long
    
    Set Dict = New Scripting.Dictionary
    
    With Dict
        For Each Sh In Worksheets
        For i = 1 To Sh.Range(rMN.Address).Cells.Count
            If Sh.Range(rMN.Address).Cells(i).Value <> "" Then
                .Item(Sh.Range(rMN.Address).Cells(i).Value) = 1
            End If
        Next i
        Next Sh
        rOut.Resize(.Count, 1).Value = Application.Transpose(.Keys)
    End With
    
End Sub