Count unique distinct values in a filtered Excel defined Table
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
- Copy above array formula
- Double press with left mouse button on cell A26 to see the prompt.
- Paste array formula to cell.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- 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.
Count values category
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Table category
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]
In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]
This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]
The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
How to use Excel Tables
Excel categories
12 Responses to “Count unique distinct values in a filtered Excel defined Table”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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?
Most Most Power Full Farmula
Ajay Kumar
I am happy you find it useful.
This is the first formula that I have found that does a unique count using Table references. Thank you!
Unfortunately it doesn't quite meet my use case.
I need to do exactly this, but instead of filtering the table, I need to feed the formula a value (either "text" hard-coded in the formula or by reference from a cell value)
Any change you have a solution to this?
Casey Missal
I need to do exactly this, but instead of filtering the table, I need to feed the formula a value (either "text" hard-coded in the formula or by reference from a cell value)
Can you provide an example and the desired output?
You absolutely nailed it. I spent day trying to figure this out then I found your solution. Thanks a lot!