Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria

[zero269]

Hello @Fluff,

This is a follow-up question regarding my earlier thread here, using your following formula:

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

The filtered results are working great. However, I wanted to create a Summary sheet that can return the same results from the entire table where one criteria is based on a cell value in Column A:

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

I thought I could simply add a 2nd criteria where if ID = D3, then it would use that to tally the Destinct values in the range of Themes.

I found this sample code from M$:

=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")

So I tried modifying your original code from this to that:

ROWS(UNIQUE(FILTER(a,a<>"")))
ROWS(UNIQUE(FILTER(a,(a<>"") * (t_Mus[ID] = D3) )))

Obviously that bombed, and I really have no clue on what I'm doing. I'd really appreciate your help on this.

Also, I discovered a similar thread here where you helped someone with a similar request, but it looks like it's only counting the visible rows and not the UNIQUE values in the range of Themes (D:F).

=IFERROR(ROWS(UNIQUE(FILTER(t_MUs[[Theme A]:[Theme C]],(t_MUs[ID]=D3)*(BYROW(t_MUs[ID],LAMBDA(br,SUBTOTAL(3,br)))=1)))),0)

Thank you,