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

1.  Select cell A26
2. Paste array formula in formula bar
3. Press and hold Ctrl + Shift
4. 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

Count unique distinct values in a filtered table.xlsx

Related posts:

Extract unique distinct values from a filtered table (udf and array formula)

Count unique values and unique distinct values in three ranges combined in excel

Count unique distinct values in a pivot table in excel

Count unique values and unique distinct values in two ranges combined

Extract unique distinct values from a related table