Xlookup to pull data and count words separated by comma

[eechristaylor]

I have a cell in one worksheet that lists counties seperated by a comma, this is column X the input is

pike, pinellas, troup

So what I am looking for is a return of 3. I can get this by using

LEN($X$4)-LEN(SUBSTITUTE($X$4,",",""))+1 but then I need to get to this field by using xlookup. I will want to look at cell B1 tthe project name and then reference the other worksheet Column A which has the project nmae and then return the "3" from the three counties in column X.

So i am trying to incorporate the LEN formula embedded in the xlookup but i get an error.

Thanks for your help.

 

[Cubist]

Try:

Book2
	A	B
1	pike, pinellas, troup	3
2	pike, pinellas	2
3	pike	1
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=COUNTA(TEXTSPLIT(A1,",",,1))

 

[Fluff]

How about like

=ROWS(TEXTSPLIT(XLOOKUP(B1,Sheet1!A2:A1000,Sheet1!X2:X1000),,","))