Count Distinct Values in Filtered Table across Multiple Columns

[zero269]

Hello,

I have a formula that counts distinct values within a range of columns.

=LET(
ThemeRng,t_MUs[[Theme A]:[Theme C]],
SUM(IF(ThemeRng <> "", 1 / COUNTIF(ThemeRng, ThemeRng), 0)))

However, I'm trying to figure out how to do the same thing when a Table column is filtered. I found a few examples online that work with a single column, just not a range of columns.

Unfiltered table shows a correct value for my distinct themes:

VBA Testing.xlsm
	A	B	C	D	E	F
1			Distinct Themes:	11
2
3	ID	MU	Descriptive Code	Theme A	Theme B	Theme C
4	P1	33	Random DC for MU 33	01 - Theme	09 - Theme	10 - Theme
5	P1	157	Random DC for MU 157	01 - Theme	03 - Theme	11 - Theme
6	P1	176	Random DC for MU 176	06 - Theme	08 - Theme
7	P1	191	Random DC for MU 191	06 - Theme	07 - Theme	10 - Theme
8	P2	89	Random DC for MU 89	02 - Theme	05 - Theme
9	P2	148	Random DC for MU 148	03 - Theme	04 - Theme	10 - Theme
10	P2	181	Random DC for MU 181	06 - Theme	09 - Theme
11	P2	297	Random DC for MU 297	01 - Theme	09 - Theme
Ct. Distinct

Cell Formulas
Range	Formula
D1	D1	=LET( ThemeRng,t_MUs[[Theme A]:[Theme C]], SUM(IF(ThemeRng <> "", 1 / COUNTIF(ThemeRng, ThemeRng), 0)))

This filtered table still shows 11. If I can count only the visible cells in the filtered table, it would return 8.

VBA Testing.xlsm
	A	B	C	D	E	F
1			Distinct Themes:	11
2
3	ID	MU	Descriptive Code	Theme A	Theme B	Theme C
4	P1	33	Random DC for MU 33	01 - Theme	09 - Theme	10 - Theme
5	P1	157	Random DC for MU 157	01 - Theme	03 - Theme	11 - Theme
6	P1	176	Random DC for MU 176	06 - Theme	08 - Theme
7	P1	191	Random DC for MU 191	06 - Theme	07 - Theme	10 - Theme
Ct. Distinct

Cell Formulas
Range	Formula
D1	D1	=LET( ThemeRng,t_MUs[[Theme A]:[Theme C]], SUM(IF(ThemeRng <> "", 1 / COUNTIF(ThemeRng, ThemeRng), 0)))

Any help would be greatly appreciated…

 

[Fluff]

How about

=LET(a,REDUCE("",t_MUs[[Theme A]:[Theme C]],LAMBDA(x,y,VSTACK(x,IF(SUBTOTAL(103,y),y,"")))),ROWS(UNIQUE(FILTER(a,a<>""))))

 

[Fluff]

Or (somewhat shorter)

=ROWS(UNIQUE(TOCOL(MAP(t_MUs[[Theme A]:[Theme C]],LAMBDA(a,IF(SUBTOTAL(103,a),a,1/0))),2)))

 

[zero269]

How about

=LET(a,REDUCE("",t_MUs[[Theme A]:[Theme C]],LAMBDA(x,y,VSTACK(x,IF(SUBTOTAL(103,y),y,"")))),ROWS(UNIQUE(FILTER(a,a<>""))))

Hello Fluff,

I went with your first one. It returned the distinct values for the filtered range of columns.

I noticed the 2nd one doesn't appear to be excluding the empty cells, and returns 1 value higher.

Results:

VBA Testing.xlsm
	A	B	C	D	E	F
1			Distinct Themes:	11
2			Filtered results:	8
3			Counts EMPTY cells >>>	9
4	ID	MU	Descriptive Code	Theme A	Theme B	Theme C
9	P2	89	Random DC for MU 89	02 - Theme	05 - Theme
10	P2	148	Random DC for MU 148	03 - Theme	04 - Theme	10 - Theme
11	P2	181	Random DC for MU 181	06 - Theme	09 - Theme
12	P2	297	Random DC for MU 297	01 - Theme	09 - Theme
Ct. Distinct

Cell Formulas
Range	Formula
D1	D1	=LET( ThemeRng,t_MUs[[Theme A]:[Theme C]], SUM(IF(ThemeRng <> "", 1 / COUNTIF(ThemeRng, ThemeRng), 0)))
D2	D2	=LET(a,REDUCE("",t_MUs[[Theme A]:[Theme C]],LAMBDA(x,y,VSTACK(x,IF(SUBTOTAL(103,y),y,"")))),ROWS(UNIQUE(FILTER(a,a<>""))))
D3	D3	=ROWS(UNIQUE(TOCOL(MAP(t_MUs[[Theme A]:[Theme C]],LAMBDA(a,IF(SUBTOTAL(103,a),a,1/0))),2)))

Thanks again Fluff, this works perfectly…

 

[Fluff]

Are the blank cells actually empty, or do they contain a formula that returns ""

 

[zero269]

Are the blank cells actually empty, or do they contain a formula that returns ""

Hi Fluff,

In my sample workbook, the cells are empty. The workbook where the formula will be used has data validation using drop-down lists in those three Theme columns.
...
I just tested your 2nd formula in my workbook and it works just like the first one.
Strange to say the least...

Disertation Research Tracker - Ashley.xlsx
	A
6	8
7	8
DRT

Cell Formulas
Range	Formula
A6	A6	=LET( a,REDUCE("",t_pAll[[Theme A]:[Theme C]],LAMBDA(x,y,VSTACK(x,IF(SUBTOTAL(103,y),y,"")))), ROWS(UNIQUE(FILTER(a,a<>""))))
A7	A7	=ROWS(UNIQUE(TOCOL(MAP(t_pAll[[Theme A]:[Theme C]],LAMBDA(a,IF(SUBTOTAL(103,a),a,1/0))),2)))

 

[Fluff]

Glad it's working & thanks for the feedback