Excel Formula to Count a Specific Value in a Range with Cells Containing Multiple Values

[NorthbyNorthwest]

Hi, everyone. Can someone help me with an Excel count formula which would count the number of times a value appears in a 4 column by 4 row range? Note: I'm excluding header row. Cells can contain a single value, multiple values separated by a comma, or no value. In the example table I want to count how many 202 values appears. The formula I’m using only counts the first value in each cell.

I'm not sure if these values are numbers or text. When I select two cells, both with a single number in them, they will sum in bottom right corner. However, if I select a cell with a single number and a cell with two numbers, the number do not sum. So, I guess they are text. Also, if these are text values, would the formula be different if a value were four characters vice three? Say, "2022."

201, 202		301
	202, 317, 411
200, 202, 213		202
	301, 305	119, 202, 207

 

[steve the fish]

Maybe try this:

=(LEN(TEXTJOIN(",",TRUE,A2:C5))-LEN(SUBSTITUTE(TEXTJOIN(",",TRUE,A2:C5),"202,","")))/LEN("202,")

The 202 could appear in a cell if you wanted, say E1:

=(LEN(TEXTJOIN(",",TRUE,A2:C5))-LEN(SUBSTITUTE(TEXTJOIN(",",TRUE,A2:C5),E1&",","")))/LEN(E1&",")

 

[NorthbyNorthwest]

Hi, Steve. Thanks so much for responding. I plugged values into an Excel worksheet using the A2:C5 range. Both formulas returned a zero. Do you think it would be better if the spaces between the numbers were removed, so the formula only had to address the comma delimiter?

 

[steve the fish]

No. Wouldnt change anything. Where do these numbers come from? What it should be doing is counting all the characters then removing 202 and counting them all again. The difference being the count of 202. Tell me what this gives:

=LEN(TEXTJOIN(",",TRUE,A2:C5))

 

[NorthbyNorthwest]

No. Wouldnt change anything. Where do these numbers come from? What it should be doing is counting all the characters then removing 202 and counting them all again. The difference being the count of 202. Tell me what this gives:

=LEN(TEXTJOIN(",",TRUE,A2:C5))

Steve, is this the entire formula? There's no reference to "202."

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1
2	201, 202		301		202	5
3		202, 317, 411				5
4	200, 202, 213		202
5		301, 305	119, 202, 207
Lists

Cell Formulas
Range	Formula
F2	F2	=LET(a,TRIM(TEXTSPLIT(TEXTJOIN(",",,A2:C5),,",")),ROWS(FILTER(a,a="202")))
F3	F3	=LET(a,TRIM(TEXTSPLIT(TEXTJOIN(",",,A2:C5),,",")),ROWS(FILTER(a,a=E2&"")))

 

[NorthbyNorthwest]

Steve, is this the entire formula? There's no reference to "202."

How about

Fluff.xlsm
	A	B	C	D	E	F
1
2	201, 202		301		202	5
3		202, 317, 411				5
4	200, 202, 213		202
5		301, 305	119, 202, 207
Lists

Cell Formulas
Range	Formula
F2	F2	=LET(a,TRIM(TEXTSPLIT(TEXTJOIN(",",,A2:C5),,",")),ROWS(FILTER(a,a="202")))
F3	F3	=LET(a,TRIM(TEXTSPLIT(TEXTJOIN(",",,A2:C5),,",")),ROWS(FILTER(a,a=E2&"")))

Fluff, you are a genius. Thanks again.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[NorthbyNorthwest]

How about

Fluff.xlsm
	A	B	C	D	E	F
1
2	201, 202		301		202	5
3		202, 317, 411				5
4	200, 202, 213		202
5		301, 305	119, 202, 207
Lists

Cell Formulas
Range	Formula
F2	F2	=LET(a,TRIM(TEXTSPLIT(TEXTJOIN(",",,A2:C5),,",")),ROWS(FILTER(a,a="202")))
F3	F3	=LET(a,TRIM(TEXTSPLIT(TEXTJOIN(",",,A2:C5),,",")),ROWS(FILTER(a,a=E2&"")))

Steve, is this the entire formula? There's no reference to "202."

Steve, even though Fluff came up with solution for me, I want to thank you for first responding so quickly and engaging with me. I'm also grateful for the explanation you provided on how the formula should work. I've been using Excel for quite a few years but have not until recently needed it capture data on strings. As a consequence, I don't have much understanding of functions related to strings. You taught me something today. Appreciate it.

 

[steve the fish]

Both what i gave a @Fluff gave produce 5 for me.