Using Counta with Let function with filter

[FryGirl]

I've searched for the possible answer, but I'm stuck as to why I can't get the correct result, in column H. Column D/E are the expected results. I'm trying to count the unique values in column B by the unique in column A.

0247 Dynamic total row - Complete.xlsx
	A	B	C	D	E	F	G	H
1	Hdr1	Hdr2		Hdr1	Hdr2		Hdr1	Hdr2
2	Apple	Car		Apple	2		Apple	1
3	Apple	Car		Orange	3		Orange	#N/A
4	Apple	Plane
5	Apple	Plane
6	Apple	Plane
7	Orange	Bus
8	Orange	Bus
9	Orange	Rocket
10	Orange	Rocket
11	Orange	Boat
Sheet1

Cell Formulas
Range	Formula
G1:H3	G1	=LET(Header1,UNIQUE($A$2:$A$11),Header2,COUNTA(UNIQUE(FILTER($B$2:$B$11,$A$2:$A$11=Header1))),VSTACK(HSTACK("Hdr1","Hdr2"),HSTACK(Header1,Header2)))
D2:D3	D2	=UNIQUE(Sheet1!$A$2:$A$11)
E2:E3	E2	=COUNTA(UNIQUE(FILTER(Sheet1!$B$2:$B$11,Sheet1!$A$2:$A$11=D2)))
Dynamic array formulas.

 

[zero269]

Are you only trying to count Apples and Oranges in A?
I ask, becasue I see you referenceing column B if it equals anything in column A; in this case Apple or Orange. However, I don't see Apple or Orange in column B, so I don't see it returning anything.
Also, you can drop the Sheet1! from the formula considering everything is on the same sheet (Sheet1).

 

[FryGirl]

I'm trying to count the unique in column B that equals apple in column A. Then I count the unique in column B that equals orange in column A.

 

[Cubist]

A few options:

Book1
	A	B	C	D	E	F	G	H
1	Hdr1	Hdr2		Hdr1	Hdr2		Hdr1	Hdr2
2	Apple	Car		Apple	2		Apple	2
3	Apple	Car		Orange	3		Orange	3
4	Apple	Plane		Total	5
5	Apple	Plane
6	Apple	Plane
7	Orange	Bus
8	Orange	Bus
9	Orange	Rocket
10	Orange	Rocket
11	Orange	Boat
Sheet1

Cell Formulas
Range	Formula
D2:E4	D2	=GROUPBY(A2:A11,B2:B11,LAMBDA(x,COUNTA(UNIQUE(x))))
G2:H3	G2	=LET(u,UNIQUE(A2:A11),HSTACK(u,MAP(u,LAMBDA(m,SUM(--(TAKE(UNIQUE(A2:B11),,1)=m))))))
Dynamic array formulas.

 

[FryGirl]

Thank you Cubist. I'll check these out when I get back to my spreadsheet, but pretty sure groupby is not and option within my version of Excel at work. Thanks again for your time.

 

[Holger]

and you could perhaps use this to get a list of HDR2 for each HDR1, which is quite similar to D2 formula already suggested.

Book8
	A	B	C	D	E	F	G	H
1	Hdr1	Hdr2		Hdr1	Hdr2		Hdr1	Hdr2
2	Apple	Car		Apple	2		Apple	2
3	Apple	Car		Orange	3		Orange	3
4	Apple	Plane		Total	5
5	Apple	Plane
6	Apple	Plane					Apple	Car, Plane
7	Orange	Bus					Orange	Bus, Rocket, Boat
8	Orange	Bus
9	Orange	Rocket
10	Orange	Rocket
11	Orange	Boat
Sheet1

Cell Formulas
Range	Formula
D2:E4	D2	=GROUPBY(A2:A11,B2:B11,LAMBDA(x,COUNTA(UNIQUE(x))))
G2:H3	G2	=LET(u,UNIQUE(A2:A11),HSTACK(u,MAP(u,LAMBDA(m,SUM(--(TAKE(UNIQUE(A2:B11),,1)=m))))))
G6:H7	G6	=DROP(GROUPBY(A2:A11,B2:B11,LAMBDA(a,ARRAYTOTEXT(UNIQUE(a)))),-1)
Dynamic array formulas.

 

[zero269]

Hi @Cubist

I'm glad you jumped in, becasue for the life of me, I couldn't figure out how to get the CountA to spill with the UNIQUE values.

I think OP wants to include the Headers. So, I just recycled the V/HSTACK's before your GROUPBY code:

VBA Testing.xlsm
	D	E
1	Hdr1	Hdr2
2	Apple	2
3	Orange	3
4	Total	5
CountUnique

Cell Formulas
Range	Formula
D1:E4	D1	=VSTACK(HSTACK("Hdr1","Hdr2"), GROUPBY(A2:A11,B2:B11,LAMBDA(x,COUNTA(UNIQUE(x)))))
Dynamic array formulas.

 

[Cubist]

GROUPBY has a [field_headers] option.

Book1
	A	B	C	D	E
1	Hdr1	Hdr2		Hdr1	Hdr2
2	Apple	Car		Apple	2
3	Apple	Car		Orange	3
4	Apple	Plane		Total	5
5	Apple	Plane
6	Apple	Plane
7	Orange	Bus
8	Orange	Bus
9	Orange	Rocket
10	Orange	Rocket
11	Orange	Boat
Sheet3

Cell Formulas
Range	Formula
D1:E4	D1	=GROUPBY(A1:A11,B1:B11,LAMBDA(x,COUNTA(UNIQUE(x))),3)
Dynamic array formulas.

 

[zero269]

GROUPBY has a [field_headers] option.

Nice! So long for V/HSTACKs...
I think I need to familairize myself with that GROUPBY function... I think it's a fairly new one...?

 

[FryGirl]

Thank you to all, and Cubist, I'll have to go with the Let version as I don't have GroupBy in my Excel version at work.