Count Unique Values excluding blanks and one value

[richard_d1]

I've got a range of letters, and I want to calculate how many unique values occur in it, excluding the letter A and empty cells.

I've used the formula

=COUNTA(UNIQUE(FILTER(A1:A10,(A1:A10<>"A")*(A1:A10<>""))))

which works fine, except when the result should be zero.

Here are some examples:

Book1
	A	B	C	D	E	F	G	H	I
1	B		A		B		A
2	C		B		B		A
3	D		C		B		A
4	B		D		B		A
5	C		A		B		A
6	D		B		B		A
7	B		C
8	C		D
9	D
10
11
12	3		3		1		1		1
Sheet1

Cell Formulas
Range	Formula
A12,I12,G12,E12,C12	A12	=COUNTA(UNIQUE(FILTER(A1:A10,(A1:A10<>"A")*(A1:A10<>""))))

The first three work, counting the number of unique values, except A and empty cells. But the last two should return 0, but don't, as COUNTA is counting #VALUE as 1.

I've tried all sorts, but can't figure out how to do it.

 

[hagia_sofia]

Hello, if you do not insist on using FILTER (which would make the formula longer) then e.g.

=SUMPRODUCT((UNIQUE(A1:A10)<>"A")*(UNIQUE(A1:A10)<>""))

 

[Fluff]

Change the CountA to Rows & then wrap the whole thing in IfError.

 

[richard_d1]

Hello, if you do not insist on using FILTER (which would make the formula longer) then e.g.

=SUMPRODUCT((UNIQUE(A1:A10)<>"A")*(UNIQUE(A1:A10)<>""))

Perfect, thanks.

 

[richard_d1]

Change the CountA to Rows & then wrap the whole thing in IfError.

That works too, many thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Domenic]

Here's another way...

=SUM(IF(LEN(UNIQUE(FILTER(A1:A11,(A1:A11<>"A"))))>0,1))