Counting Non-Duplicated Entries Across Multiple Sheets

[lykar]

I have a workbook that I use to keep track of volunteers. One sheet per month, along with a totals sheet. I use the formula =SUMPRODUCT((B7:B150<>"")/COUNTIF(B7:B150,B7:B150&"")) to count the number of unduplicated volunteers per month, but I need to count the number of unduplicated volunteers per year. Some volunteers come in over multiple months, and I need a formula that will only count them once per year. I am somewhat familiar with excel, but not an expert by any means. Any help would be greatly appreciated.

 

[Eric W]

It is possible to create a native formula to do that, but it would be very complicated. I wrote a UDF (User Defined Function) that is a bit simpler. If you'd like to try it, follow these steps:

1) Open your workbook to the sheet where you want the formula
2) Right click on the sheet tab on the bottom and select "View Code"
3) From the menu bar in the VBA editor, select Insert > Module
4) Paste the following code into the window that opens:

Function CountVolunteers(MyRange As Range, ParamArray Excludes())
Dim MyDict As Object, MyStr As String, i As Integer

    Application.Volatile
    On Error Resume Next
    Set MyDict = CreateObject("Scripting.Dictionary")
    
    For Each sht In Worksheets
    
        For i = LBound(Excludes) To UBound(Excludes)
            If sht.Name = CStr(Excludes(i)) Then GoTo NextSht:
        Next i
        
        For Each cel In sht.Range(MyRange.Address)
            MyStr = cel.Value
            If MyStr <> "" Then MyDict.Add MyStr, 1
        Next cel

NextSht:
    Next sht
    
    CountVolunteers = MyDict.Count
            
End Function

5) Close the VBA editor by pressing Alt-Q
6) In the cell where you want the formula, enter:

=Countvolunteers(B7:B150,"Totals")

where B7:B150 is the range (must be the same on all sheets), and "Totals" is the name of the totals sheet which won't be counted. If you have multiple sheets you don't want to count, you can add them to the list:

=Countvolunteers(B7:B150,"Totals","Summary")

Let me know how that works.