Count Unique Items in Excel Filtered List

image You can use the SUBTOTAL function to count visible items in a filtered list. In today’s example, AlexJ shows how to count the unique visible items in a filtered list. So, if an item appears more than once in the filtered results, it would only be counted once. Thanks, AlexJ!

Count the Unique Items

In the screenshot below, the formula in column D shows a 1 for the first instance of each item, and a 0 for all subsequent instances. For example, in cell D7, there’s a 1, counting item 91-AB145, and in the next two rows the count for the same item is zero.

The formula in cell D5 is:

=(COUNTIF($C$5:$C5,$C5)<2)*1

FilterUnique01

Apply a Filter

However, when the list is filtered to show only the Central region items, the total in cell D2 still shows 16 unique items, and cell D11 shows a zero for item 91-AB146, even though it’s the first visible instance of that item.

FilterUnique02

To solve the problem, AlexJ added some helper columns to the table. First, in column E, he created a formula to check if the row is visible. The formula in cell E5 is:

=1*SUBTOTAL(3,$D5)

AlexJ uses the 1 multiplier at the start of the formula to avoid the problem of the last row not being included in the AutoFilter range.

FilterUnique03

Based on this new formula, the total in cell E2 correctly shows that only 6 rows are visible.

Test For Visible Rows

Next, in column F, AlexJ created a formula to show the item name if the row is visible. For hidden rows, the formula displays a hyphen instead of the item name. In cell F5, the formula is:

=IF($E5,$C5,”-“)

In cell G1, I tested the result for hidden cell F6, and you can see the result is a hyphen.

FilterUnique04

Count Unique Visible Items

Finally, in column G, AlexJ created a formula to check for unique items in column F, where only the visible rows have an item name. In cell G5, the formula is:

=($F5<>”-“)*(COUNTIF($F$5:$F5,$F5)<2)

With this formula, the hidden rows are ignored, and the count of unique items in cell G2 is correct for the filtered rows. The item 91-AB145 is counted only once, even though it’s in the filtered results twice, and item 91-AB146 is counted, even though it’s not the first instance of that item in the full list.

FilterUnique05

Array Formula to Count Unique

Another way to count unique items in a filtered list, is with named ranges and an array formula, as described in the July 2001 issue of Excel Experts E-letter (EEE), by David Hager. David’s formula is in cell F3 below, and shows the same results as AlexJ’s formula in cell G3.

Make sure you do a few warm up stretches before you attempt this one!

FilterUnique06

Download the Sample File

To see AlexJ’s formulas, you can download Alex’s sample file from the Contextures website. On the AlexJ Sample Files page, go to the Filters section, and look for:  FL0001 – Count Unique Items in Filtered List

_______________

You may also like...

5 Responses

  1. Raul says:

    I have a problem guys and i need to sort it out just by todays end or i land in a problem.

    Hi
    i have written the following code , which searchs through various columns and basically filters my data and then copies it to another worksheet, but i am facing one problem, the source data has a column containing hyperlinks to various other files on the system, but the copied data does not copy the hyperlink, i have to give a presentation at my college and need to sort this problem out by today afternoon, please help me out with the same.

    The code is ‘

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCrit As Range

    Set rngCrit = wksCrit.Range(“CriteriaRng”)
    Application.EnableEvents = False

    Select Case Target.Address
    Case Range(“SelReg”).Address
    rngCrit.Cells(2, 1).Value = Target.Value
    Case Range(“Selcountry”).Address
    rngCrit.Cells(2, 2).Value = Target.Value
    Case Range(“SelCount”).Address
    rngCrit.Cells(2, 3).Value = Target.Value
    Case Range(“SelCity”).Address
    rngCrit.Cells(2, 4).Value = Target.Value
    Case Range(“SelDate”).Address
    rngCrit.Cells(2, 5).Value = Target.Value
    Case Range(“WhHotN”).Address
    rngCrit.Cells(2, 6).Value = Target.Value
    Case Range(“WhResN”).Address
    rngCrit.Cells(2, 7).Value = Target.Value
    Case Range(“WhOffN”).Address
    rngCrit.Cells(2, 8).Value = Target.Value
    Case Range(“WhRetN”).Address
    rngCrit.Cells(2, 9).Value = Target.Value

    End Select

    If Range(“SelReg”).Value = “” Then
    rngCrit.Cells(2, 1).ClearContents
    End If
    If Range(“Selcountry”).Value = “” Then
    rngCrit.Cells(2, 2).ClearContents
    End If
    If Range(“SelCount”).Value = “” Then
    rngCrit.Cells(2, 3).ClearContents
    End If
    If Range(“SelCity”).Value = “” Then
    rngCrit.Cells(2, 4).ClearContents
    End If
    If Range(“SelDate”).Value = “” Then
    rngCrit.Cells(2, 5).ClearContents
    End If
    If Range(“WhHotN”).Value = “” Then
    rngCrit.Cells(2, 6).ClearContents
    End If
    If Range(“WhResN”).Value = “” Then
    rngCrit.Cells(2, 7).ClearContents
    End If
    If Range(“WhOffN”).Value = “” Then
    rngCrit.Cells(2, 8).ClearContents
    End If
    If Range(“WhRetN”).Value = “” Then
    rngCrit.Cells(2, 9).ClearContents
    End If

    If Not rngCrit Is Nothing Then
    wksResRep.Range(“B1:W65?).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=rngCrit, _
    CopyToRange:=Range(“ExtractDetails”), Unique:=False
    End If

    exitHandler:
    Application.EnableEvents = True
    Exit Sub
    errHandler:
    Resume exitHandler

    End Sub

    Please reply back soon.
    Thanks

  2. Darrell says:

    Wonderful work. I am surprised that there isn’t a subtotal function that exists in Excel to handle this calculation. I’m very impressed and am excited that such a niche need was fulfilled…great site! I’m now a new fan!

  3. Eric says:

    I found this on another website. I don’t ubnderstand it, but it works and no need for extra helper cells:

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),MATCH(“~”&A2:A100,A2:A100&””,0)),ROW(A2:A100)-ROW(A2)+1),1))

    …be sure to hit CONTROL+SHIFT+ENTER after pasting this formula. This is for a range of A2:A100, adjust the range accordingly.

    Source:
    http://www.mrexcel.com/forum/excel-questions/395996-count-unique-values-filtered-list.html
    (see 3rd comment down)

  4. Naresh says:

    123456

    Exmple:
    i have write some text in only one cell so can I count this?

    which text type in only one cell….
    Please answer

Leave a Reply to Eric Cancel reply

Your email address will not be published. Required fields are marked *