Count unique values across multiple columns ignore blanks

[xlxllama]

From the following example data set I need to count the number of chemicals that appear at least once in the selected range, but not their duplicates, and ignore any blank cells. I know the answer for this set should be 21, but I can't figure out how to create a formula to give me that answer. All of the examples of counting unique values I can find are only for a single column.

Ethylhexyl phthalate

BPA

Ethylbenzene

Styrene

Phenol

Antimony

Arsenic

Ethylbenzene

Styrene

Phenol

Ethylhexyl diphenyl phosphate

Ethylhexanoic acid

Antimony

Arsenic

Ethylbenzene

Styrene

4-NP

Acrylonitrile

Ethylene glycol

Toluene

Phenol

DINP

Formaldehyde

Antimony

Arsenic

Cobalt

Acetaldehyde

Phthalic anhydride

BBP

Tris phosphate

Toluene

Styrene

Phenol

Cobalt

Antimony

Arsenic

Ethylbenzene

Toluene

Styrene

Ethylene glycol

Decabromodiphenyl ethane

Antimony

Arsenic

Cobalt

Styrene

Ethylene glycol

Cobalt

Ethylbenzene

Styrene

4-NP

Acrylonitrile

Ethylene glycol

Toluene

Phenol

Tris phosphate

DINP

Formaldehyde

Antimony

Arenic

Cobalt

Acetaldehyde

Phthalic anhydride

BBP

Ethylbenzene

 

[lrobbo314]

How about this?

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Ethylhexyl phthalate	BPA
2	Ethylbenzene	Styrene	Phenol	Antimony	Arsenic
3	Ethylbenzene	Styrene	Phenol	Ethylhexyl diphenyl phosphate	Ethylhexanoic acid	Antimony	Arsenic
4	Ethylbenzene	Styrene	4-NP	Acrylonitrile	Ethylene glycol	Toluene	Phenol	DINP	Formaldehyde	Antimony	Arsenic	Cobalt	Acetaldehyde	Phthalic anhydride	BBP	Tris phosphate
5	Toluene	Styrene	Phenol	Cobalt
6	Antimony	Arsenic
7	Ethylbenzene	Toluene	Styrene	Ethylene glycol	Decabromodiphenyl ethane
8	Antimony	Arsenic	Cobalt
9	Styrene	Ethylene glycol	Cobalt
10	Ethylbenzene	Styrene	4-NP	Acrylonitrile	Ethylene glycol	Toluene	Phenol	Tris phosphate	DINP	Formaldehyde	Antimony	Arenic	Cobalt	Acetaldehyde	Phthalic anhydride	BBP
11	Ethylbenzene
12
13
14	4-NP
15	Acetaldehyde
16	Acrylonitrile
17	Antimony
18	Arenic
19	Arsenic
20	BBP
21	BPA
22	Cobalt
23	Decabromodiphenyl ethane
24	DINP
25	Ethylbenzene
26	Ethylene glycol
27	Ethylhexanoic acid
28	Ethylhexyl diphenyl phosphate
29	Ethylhexyl phthalate
30	Formaldehyde
31	Phenol
32	Phthalic anhydride
33	Styrene
34	Toluene
35	Tris phosphate
36	Total Chemicals: 22
Sheet1

Cell Formulas
Range	Formula
A14:A36	A14	=LET(tbl,A1:P11,r,ROWS(tbl),seq,SEQUENCE(r*COLUMNS(tbl),,0),sc,INDEX(tbl,MOD(seq,r)+1,INT(seq/r)),uc,SORT(UNIQUE(FILTER(sc,sc<>0,""))),IFERROR(INDEX(uc,SEQUENCE(ROWS(uc)+1)),"Total Chemicals: " & COUNTA(uc)))
Dynamic array formulas.

 

[jasonb75]

I know the answer for this set should be 21

It's actually 22 (one of them might be a typo, but in formula terms it is still different).
Edit range as required. This will need to be array confirmed with Ctrl Shift Enter if you don't have the dynamic array update.

=SUM(IFERROR(1/COUNTIF(A1:P11,A1:P11),0))

 

[xlxllama]

This work! Thanks!

It's actually 22 (one of them might be a typo, but in formula terms it is still different).
Edit range as required. This will need to be array confirmed with Ctrl Shift Enter if you don't have the dynamic array update.

=SUM(IFERROR(1/COUNTIF(A1:P11,A1:P11),0))