get-digital-help.com

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table.

Debra Dalgleish described in an article 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 that also was inspired by Laurent Longre's formula.

The Excel defined Table shown in the image above is named Table2. There are forenames in column A. The table is filtered with "Finland" and "Mexico" in column C, the following array formula counts unique distinct values based on a filter applied to an Excel defined Table.

What you will learn in this article

• Build a formula that counts unique distinct values in a filtered table using a modified version of Laurent Longre's formula.
• Identify filtered values using the SUBTOTAL and OFFSET function.
• How to reference a column in an Excel defined Table, in a formula.
• Convert boolean values to numerical equivalents
• Convert text values to unique numbers based on the order as if they had been sorted from A to Z.
• Count unique distinct numbers

Array formula in A26:

How to create an array formula

1. Copy above array formula
2. Double click cell A26 to see the prompt.
3. Paste array formula to cell.
4. Press and hold Ctrl + Shift simultaneously.
5. Press Enter once.
6. Release all keys.

Explaining array formula in cell A26

Step 1 - Create an array of ranges

When you use an array of numbers in OFFSETs second argument an array of ranges is returned, this makes it possible to evaluate each value using the SUBTOTAL function.

The MATCH function creates an array of numbers in sequence from 1 to as many as there are values in column 'First Name' in Excel defined Table Table2.

MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))

becomes

MATCH({2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21}, {2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21})

and returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}

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 the "Evaluate formula" feature found on tab "Formula" on the ribbon.

Step 2 - Find visible values

The SUBTOTAL function is capable of identifying filtered values if you use the OFFSET function demonstrated in the previous step.

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 the following array:

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

0 (zero) is a hidden value and 1 is a visible value in the Excel defined Table.

Step 3 - Calculate rank by alphabetical order

The COUNTIF function lets you assign a unique number to all values based on the order if they were sorted from A to Z or vice versa. Simply append a less than or greater than character to the second argument in the COUNTIF function.

This step is necessary in order to count unique distinct values, duplicates will have the same number assigned to them which is handy in this case.

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

The IF function replaces filtered values with the numbers calculated in the previous step.

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

The FREQUENCY function counts each number in the array and returns the total at the same relative psoition as the first occuring number.

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

The SUM function adds all values if they are larger than 0 (zero), first we need to check if the total is larger than zero, TRUE or FALSE are returned. Then we need to convert the boolean values to their numerical equivalent so the SUM function will be able to add numbers and return a total.

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. Kaya, Fraiser and Jui are the unique distinct names and they are three which is correct.