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

 

[Fluff]

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

Agreed, although they could different results depending on what the actual data is.

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

Cell Formulas
Range	Formula
F2	F2	=LET(a,TRIM(TEXTSPLIT(TEXTJOIN(",",,A2:C5),,",")),ROWS(FILTER(a,a="202")))
G2	G2	=(LEN(TEXTJOIN(",",TRUE,A2:C5))-LEN(SUBSTITUTE(TEXTJOIN(",",TRUE,A2:C5),"202,","")))/LEN("202,")
F3	F3	=LET(a,TRIM(TEXTSPLIT(TEXTJOIN(",",,A2:C5),,",")),ROWS(FILTER(a,a=E2&"")))
G3	G3	=(LEN(TEXTJOIN(",",TRUE,A2:C5))-LEN(SUBSTITUTE(TEXTJOIN(",",TRUE,A2:C5),E2&",","")))/LEN(E2&",")

 

[NorthbyNorthwest]

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

No doubt. I was not arriving at right answer when I plugged formula in Excel. But then you explained how the formula worked. So, I started counting manually based on your explanation. And, found I was getting close to answer. So, it was clear to me that I wasn't fully understanding. Thanks, again.

 

[steve the fish]

Good point. I had thought of that but not at the start of the number stupidly. Easily fixed mind.

 

[steve the fish]

@NorthbyNorthwest thanks glad to help. Nice when it helps people to help themselves next time.