Count unique text in range

[Nick70]

Hi,

I would like to count all cells which contain text in a specific range excluding if text is "not available".

So for example if in sheet1 range(B4:B100) we have text: 'Pear', 'Apple', 'Cherry' and 'not available' multiple times then the count should return 3 no matter how many cells have text 'Pear', 'Apple', 'Cherry' or 'not available'.

Considering we need to exclude duplicates I think we would need VBA as opposed to COUNTIF function.

Thanks,
Nix

 

[etaf]

as you have 365 version
=COUNTA(UNIQUE(FILTER(B4:B100,(B4:B100<>"not available")*(B4:B100<>""))))

Book9
	A	B	C	D
1
2
3
4		pear		5
5		pear
6		apple
7		apple
8		apple
9		fred
10		harry
11		cherry
12		harry
13		not available
14		pear
15		pear
16		apple
17		apple
18		apple
19		fred
20		harry
21		cherry
22		harry
23		not available
24		pear
25		pear
26		apple
27		apple
28		apple
29		fred
30		harry
31		cherry
32		harry
33		not available
34
Sheet1

Cell Formulas
Range	Formula
D4	D4	=COUNTA(UNIQUE(FILTER(B4:B100,(B4:B100<>"not available")*(B4:B100<>""))))

 

[Georgiboy]

Here is an idea:

Book1
	B	C	D
4	Pear		3
5	Apple
6	Cherry
7	not available
8	Pear
9	Apple
10	Cherry
11	not available
12	Pear
13	Apple
14	Cherry
15	not available
16	Pear
17	Apple
18	Cherry
19	not available
20	Pear
21	Apple
22	Cherry
23	not available
24	Pear
25	Apple
26	Cherry
27	not available
28	Pear
29	Apple
30	Cherry
31	not available
32	Pear
33	Apple
34	Cherry
35	not available
36	Pear
37	Apple
38	Cherry
39	not available
40	Pear
41	Apple
42	Cherry
43	not available
44	Pear
45	Apple
46	Cherry
47	not available
48	Pear
49	Apple
50	Cherry
51	not available
52	Pear
53	Apple
54	Cherry
55	not available
56	Pear
57	Apple
58	Cherry
59	not available
60	Pear
61	Apple
62	Cherry
63	not available
64	Pear
65	Apple
66	Cherry
67	not available
68	Pear
69	Apple
70	Cherry
71	not available
72	Pear
73	Apple
74	Cherry
75	not available
76	Pear
77	Apple
78	Cherry
79	not available
80	Pear
81	Apple
82	Cherry
83	not available
84	Pear
85	Apple
86	Cherry
87	not available
88	Pear
89	Apple
90	Cherry
91	not available
92	Pear
93	Apple
94	Cherry
95	not available
96	Pear
97	Apple
98	Cherry
99	not available
100	Pear
Sheet1

Cell Formulas
Range	Formula
D4	D4	=LET(r,TOCOL(B4:B100,1),ROWS(UNIQUE(FILTER(r,r<>"not available"))))

 

[duggie33]

Try this...

Book1
	B
3	3
4	pear
5	apple
6	cherry
7	not available
8	pear
9	pear
10	pear
11	apple
12	cherry
13	not available
14	cherry
15	not available
16	pear
17	apple
18	cherry
19	not available
Sheet3

Cell Formulas
Range	Formula
B3	B3	=LET(rng,B4:B19,uniq,UNIQUE(rng),COUNTA(FILTER(uniq,uniq<>"not available")))

Hope that helps,

Doug

 

[Nick70]

Thanks to all.

=COUNTA(UNIQUE(FILTER(B4:B100,(B4:B100<>"not available")*(B4:B100<>"")))) works fine I get 2

=LET(r,TOCOL(B4:B100,1),ROWS(UNIQUE(FILTER(r,r<>"not available")))) I get #NAME

=LET(rng,B4:B19,uniq,UNIQUE(rng),COUNTA(FILTER(uniq,uniq<>"not available"))) I get 3 as result instead of 2

Nic

 

[etaf]

you are welcome
I suspect the
=LET(rng,B4:B100,uniq,UNIQUE(rng),COUNTA(FILTER(uniq,uniq<>"not available"))) I get 3 as result instead of 2
may also see blank cells in the range as a unique value - hence 3
=LET(rng,B4:B100,uniq,UNIQUE(rng),COUNTA(FILTER(uniq,(uniq<>"not available")*(uniq<>""))))

Book9
	A	B	C	D	E	F
1
2
3
4		pear		5	5
5		pear
6		apple
7		apple
8		apple
9		fred
10		harry
11		cherry
12		harry
13		not available
14		pear
15		pear
16		apple
17		apple
18		apple
19		fred
20		harry
21		cherry
22		harry
23		not available
24		pear
25		pear
26		apple
27		apple
28		apple
29		fred
30		harry
31		cherry
32		harry
33		not available
34
35
Sheet1

Cell Formulas
Range	Formula
D4	D4	=COUNTA(UNIQUE(FILTER(B4:B100,(B4:B100<>"not available")*(B4:B100<>""))))
E4	E4	=LET(rng,B4:B100,uniq,UNIQUE(rng),COUNTA(FILTER(uniq,(uniq<>"not available")*(uniq<>""))))

 

[Georgiboy]

=LET(r,TOCOL(B4:B100,1),ROWS(UNIQUE(FILTER(r,r<>"not available")))) I get #NAME

You probably don't have access to the TOCOL function in your version of 365

 

[Nick70]

probably yes! txs