Forum Discussion

Samphire's avatar
Samphire
Copper Contributor
Oct 10, 2023

Counting unique values across multiple tabs

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.

 

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 10, 2023

    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.

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 10, 2023

    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.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 10, 2023

    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.

     

     

     

     

Resources