## Count unique distinct values in a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a formula by Laurent Longre found here: Excel Experts E-letter from John Walkenbach's web site.

I remember a post I did about extracting unique distinct values from a filtered table (udf and array formula 10 months ago. That formula was also inspired by Laurent Longre's formula.

In this post I am going to demonstrate how to count unique distinct values and records in a filtered table using a modified version of Laurent Longre's formula.

Table name: Table2

There are forenames in column A. The table is filtered with "Finland" and "Mexico" in column C.

**Array formula in A26:**

### How to create an array formula

- Select cell A26
- Paste array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter

### Explaining array formula in cell A26

*Step 1 - Create an array of ranges*

When an array of numbers is used in OFFSET´s second argument an array of ranges is returned. This makes it possible to evaluate each value using the subtotal function.

OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)

becomes

OFFSET(Table2[First Name], {0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}, 0, 1)

and returns (if you press F9 in formula bar)

{"Fraser"; "Kaya"; "Horace"; "Kelton"; "Linnie"; "Jui"; "Mckinley"; "Emmit"; "Siena"; "Cyril"; "Tiara"; "Charley"; "Kaya"; "Fraser"; "Fraser"; "Lynna"; "Jui"; "Lynna"; "Damien"; "Spencer"}

or returns an array of #VALUE errors if you use "Evaluate formula" dialog box, tab "Formula".

**Step 2 - Find visible values**

SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1))

becomes

SUBTOTAL(3, {"Fraser"; "Kaya"; "Horace"; "Kelton"; "Linnie"; "Jui"; "Mckinley"; "Emmit"; "Siena"; "Cyril"; "Tiara"; "Charley"; "Kaya"; "Fraser"; "Fraser"; "Lynna"; "Jui"; "Lynna"; "Damien"; "Spencer"})

and returns

{0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 1; 1; 0; 1; 0; 0; 0}

**Step 3 - Calculate rank by alphabetical order**

COUNTIF(Table2[First Name], "<"&Table2[First Name])

returns

{18; 2; 14; 8; 14; 4; 4; 10; 0; 19; 1; 17; 3; 16; 8; 13; 10; 7; 10; 4}

**Step 4 - Filter visible value's alphabetical rank**

IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), COUNTIF(Table2[First Name], "<"&Table2[First Name]), "")

becomes

IF({0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 1; 1; 0; 1; 0; 0; 0}, {18; 2; 14; 8; 14; 4; 4; 10; 0; 19; 1; 17; 3; 16; 8; 13; 10; 7; 10; 4}, "")

and returns

{""; ""; ""; 8; ""; 4; 4; 10; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""}

**Step 5 - Calculate frequency**

FREQUENCY(IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), COUNTIF(Table2[First Name], "<"&Table2[First Name]), ""), COUNTIF(Table2[First Name], "<"&Table2[First Name]))

becomes

FREQUENCY({""; ""; ""; 8; ""; 4; 4; 10; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""},{18; 2; 14; 8; 14; 4; 4; 10; 0; 19; 1; 17; 3; 16; 8; 13; 10; 7; 10; 4})

and returns

{0, 0, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

**Step 6 - Sum values larger than zero**

SUM(--(FREQUENCY(IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), COUNTIF(Table2[First Name], "<"&Table2[First Name]), ""), COUNTIF(Table2[First Name], "<"&Table2[First Name]))>0))

becomes

SUM(--({0, 0, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}>0))

becomes

SUM(--({FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}))

becomes

SUM({0;0;0;1;0;1;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0})

and returns 3 in cell A26.

### Download excel *.xlsx file

### Category: Count values

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Count unique distinct values in a column

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: First, let me explain […]Comments(26) Filed in category: Count unique distinct values, Count values, Excel

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

Count number of times a string exist in multiple cells using excel formula

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]Comments(19) Filed in category: Count text values, Count values, Excel

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

### Category: Unique distinct values

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]Comments(79) Filed in category: Excel, Unique distinct values

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Extract a unique distinct list by matching items that meet a criterion in excel

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Comments(40) Filed in category: Excel, Unique distinct values

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]Comments(31) Filed in category: Excel, Unique distinct values

### 7 Responses to “Count unique distinct values in a filtered table”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Thanks so very much.. you saved my day with your wisdom :-)

grz from holland

Louis,

Thank you for commenting!

[...] Count unique distinct values in a filtered table [...]

This is great but I have a problem. I am counting a field that has text and numbers (stored as text). The formula is treating the numbers stored as text like they are the same. For example, if my values are a1,b1,1,2 the formula returns 3. Do you know how to fix this?

Jamie, the formula counts visible unique distinct values in a single column. Can you explain in greater detail?

Sure...in your example you have the first name Kaya twice. If you change the first Kaya to a "1" and the second instance to a "2" so that your visible cells are 1,2,Fraser, Fraser, Jui the count is 4 as it should be UNTIL you filter by names instead of country. Filter by names and select the 1, the 2, and one of the other names and you will see the count off. (For me, it doesn't matter what column I filter by though...if there are numbers in my results then the counts are off.)

This doesn't seem to work in Excel for Mac 2011 version 14.4.1, see screenshot at https://postimg.org/image/cotsj3mhr/

Using OS X Mavericks.

Any idea why?