Counting unique values across multiple tabs

[Samphire]

Hello.

I'm trying to write a formula but am having no luck - help please.

I would like to count all unique values (these are client id numbers) in column A across multiple sheets (only counting the client's id number once across all sheets). I only want to count the clients for whom column B is not blank.



We do not have Unique function available as we are using Microsoft office 2013.



I have attached a sample workbook to demonstrate the problem (I have put all the data onto one sheet, but where it says 'sheet 1' 'sheet 2' etc, this represents different sheets in our workbook)

The formula needs to account for blank spaces in column A as we want to use it for live data.

[#2]

@Samphire 

=SUM(N(IF((M1:M22=G4)*NOT(ISBLANK(F1:F22)),MATCH(IF((M1:M22=G4)*NOT(ISBLANK(F1:F22)),A1:A22),IF((M1:M22=G4)*NOT(ISBLANK(F1:F22)),A1:A22),)=ROW(1:22))))

You can try this formula to count unique values in column A within one worksheet in Excel 2013 with conditions that column M equals the value in G4 and that column F isn't blank. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

 

[#3]

@Samphire 

Here is a custom VBA function you can use:

Function CountUnique() As Long
    Dim wsh As Worksheet
    Dim r As Long
    Dim m As Long
    Dim c As New Collection
    Application.Volatile
    On Error Resume Next
    For Each wsh In Worksheets
        Select Case wsh.Name
            Case "Summary"
                ' Skip this sheet
            Case Else
                m = wsh.Range("A" & wsh.Rows.Count).End(xlUp).Row
                For r = 2 To m
                    If wsh.Range("F" & r).Value <> "" Then
                        c.Add Item:=1, Key:=CStr(wsh.Range("A" & r).Value)
                    End If
                Next r
        End Select
    Next wsh
    CountUnique = c.Count
End Function

Use like this in a cell:

=CountUnique()

Save the workbook as a macro-enabled workbook and make sure that you allow macros when you open the workbook.

[#4]

@Samphire 

=SUM(N(IF(NOT(ISBLANK(B5:B31)),MATCH(IF(NOT(ISBLANK(B5:B31)),A5:A31),IF(NOT(ISBLANK(B5:B31)),A5:A31),)=ROW(A1:A27))))

You can try this formula for your changed requirement. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.