Count number of cells in range that contain non-specific text

[AVO051]

I’m reaching out regarding a Microsoft Forms multiple-choice survey report I received in Excel. I’ve been working on visualizing the responses in Excel and have successfully created graphs for most of the survey questions. However, I have encountered a challenge with the "Other" category.

Since respondents were able to input free text under "Other," I would like to count the number of rows that contain text in this category. While I have managed to identify rows with non-specified text using a formula, I haven’t been able to correctly apply the SUMPRODUCT function to sum up these rows accurately.

For a single row:
=LEN(SUBSTITUTE(A2;";";""))>SUMPRODUCT((COUNTIF(A2;"*"&$H$2:$H$7&"*"))*LEN($H$2:$H$7))
returns TRUE or FALSE
with A2: cell with the selected multiple-choice ioptions (e.g. Option003;Option00005;Other text)
$H$2:$H$7: a variable list with all multiple-choice ioptions (e.g. Option1, Option02, Option003, Option0004, Option00005, Option000006, erc.)

For a range:
=SUMPRODUCT(LEN(SUBSTITUTE(A2:A12;";";""))>(COUNTIF(A2:A12;"*"&$H$2:$H$7&"*"))*(LEN($H$2:$H$7)))
returns #N/A instead of 3

I have analyzed both parts of the formula I’m using, and it seems that the second part isn’t working as it should. Despite my troubleshooting efforts, I haven’t been able to pinpoint the issue.

Answers Q17	Single row	Part 1	Part 2	Range	Q17 options
Random text	TRUE	11	7	#N/A	Option1
New text	TRUE	8	0		Option02
Option003	FALSE	9	9		Option003
	FALSE	0	0		Option0004
Option003	FALSE	9	11		Option00005
Option1;Option003;	FALSE	16	12		Option000006
	FALSE	0
	FALSE	0
Option1;	FALSE	7
Option000006;	FALSE	12
Option003;Option00005;Other text	TRUE	30

Would you happen to know of a better approach or formula to resolve this problem? Any suggestions or tips you can provide would be greatly appreciated.

Thank you for your time and support.

 

[DanteAmor]

This may be an option.
Put the formula in B2 and copy it down.

=IF(IFERROR(MATCH(0,BYROW(TEXTSPLIT(A2,,";",TRUE),LAMBDA(br,COUNTIF(H$2:H$7,br))),0),FALSE),TRUE)

 

[AVO051]

This may be an option.
Put the formula in B2 and copy it down.

=IF(IFERROR(MATCH(0,BYROW(TEXTSPLIT(A2,,";",TRUE),LAMBDA(br,COUNTIF(H$2:H$7,br))),0),FALSE),TRUE)

I'm looking for a solution that doesn't require adding columns, as I have multiple columns with the same issue, and extra formula columns are increasing the file size.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Answers Q17	Single row	Part 1	Part 2	Range			Q17 options
2	Random text	TRUE	11	7	3			Option1
3	New text	TRUE	8	0				Option02
4	Option003	FALSE	9	9				Option003
5		FALSE	0	0				Option0004
6	Option003	FALSE	9	11				Option00005
7	Option1;Option003;	FALSE	16	12				Option000006
8		FALSE	0
9		FALSE	0
10	Option1;	FALSE	7
11	Option000006;	FALSE	12
12	Option003;Option00005;Other text	TRUE	30
Data

Cell Formulas
Range	Formula
E2	E2	=SUM(MAP(TOCOL(A2:A12,1),LAMBDA(m,SUM(--(COUNTIFS(H2:H7,TEXTSPLIT(m,";",,1))=0)))))

 

[AVO051]

Thank you both for your help! Your insights gave me a new perspective on Excel, leading to this formula as part of a SUMPRODUCT with multiple criteria. I really appreciate it!

=SUMPRODUCT((BYROW(VALUE($AX2:$BB12);LAMBDA(scores;SUM(--(scores<=3))>0)))*(BYROW(TEXTSPLIT(A2:A12;";");LAMBDA(textsplit;SUM(--(COUNTIF(H2:H7;textsplit)))=0))))

 

[Fluff]

Glad we could help & thanks for the feedback.