Using TEXTSPLIT to spill down a list

[kitty_el]

I’m really struggling with this problem, I have a list of data in one column. The data is organised as words in each cell for example cat,dog,horse,fish - I need to be able to reference the whole column as the criteria in a SUMIFS. I’ve tried =TEXTSPLIT(D3:D66, “,”) but this just returns an error. I tried incorporating TEXTJOIN but this still didn’t work.
Does anyone have any ideas?

 

[HighAndWilder]

I’m really struggling with this problem, I have a list of data in one column. The data is organised as words in each cell for example cat,dog,horse,fish - I need to be able to reference the whole column as the criteria in a SUMIFS. I’ve tried =TEXTSPLIT(D3:D66, “,”) but this just returns an error. I tried incorporating TEXTJOIN but this still didn’t work.
Does anyone have any ideas?

Try this where A2:A47 contains the animal names and B2:B47 contains the values to sum.

=SUMIF(A2:A47,"Cat",B2:B47)

If you have criteria in two columns do this where C4:C49 contains the values to sum, A4:A49 contains the animal names, "Cat" is the criteria for that column and
B4:B49 contains the second column with a criteria and 1 is the criteria.

=SUMIFS(C4:C49,A4:A49,"Cat",B4:B49,1)

Are you using SUMIFS and only have one criteria?

 

[kitty_el]

Each cell in the column D3:D66 contains multiple words for example, cat, dog - all separated by a comma. I need to use each of these words as the criteria, that is why I was trying to use TEXTSPLIT, which works fine on one cell but when I try and reference the whole column I get a #VALUE

 

[Cubist]

Please post a sample of your data and what you're trying to accomplish.

 

[kitty_el]

It’s just a column D3:D66, each cell so D3 contains words like cat,dog,fish
I just need to know how to use TEXTSPLIT to reference the whole column so this doesn’t work - =TEXTSPLIT(D3:D66, “,”) and just return an error.

 

[Cubist]

Without seeing your expected output we can only guess. Maybe this?

Book1
	D	E
1
2	cat,dog,fish	cat
3	apple,bananna	dog
4	cake,juice	fish
5		apple
6		bananna
7		cake
8		juice
Sheet5

Cell Formulas
Range	Formula
E2:E8	E2	=DROP(REDUCE("",D2:D4,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,,",")))),1)
Dynamic array formulas.

 

[Phuoc]

Another way:

Book1
	D	E
1
2	cat,dog,fish	cat
3	apple,bananna	dog
4	a,123	fish
5		apple
6		bananna
7		a
8		123
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=TOCOL(TEXTSPLIT(TEXTJOIN(",",1,D2:D4),","))
Dynamic array formulas.

 

[Peter_SSs]

.. or

24 11 11.xlsm
	D	E
1
2	cat,dog,fish	cat
3	apple,bananna	dog
4	a,123	fish
5		apple
6		bananna
7		a
8		123
9
Split

Cell Formulas
Range	Formula
E2:E8	E2	=TEXTSPLIT(TEXTJOIN(",",1,D2:D4),,",")
Dynamic array formulas.