Counting unique values across multiple tabs

Copper Contributor

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.

3 Replies

Re: Counting unique values across multiple tabs

``=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.

Re: Counting unique values across multiple tabs

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
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.

Re: Counting unique values across multiple tabs

``=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.