Counting text in a range that may appear multiple times in a cell

[AnnaBanana4]

Hello All,
I'm looking to find a way to count the number of occurrences of text in a range, including multiples in the same cell.

For example, I have this:

To count "101"s in the list, I have this formula in D2: =COUNTIF(A$2:A$7,"*"&$C2&"*")
But this formula doesn't seem to take into account multiples in the same line. As well, I have no idea why "106" isn't counting.

Is there a way to do this in excel without VBA?

Thanks in advance!

 

[Domenic]

Enter the following formula in D2, and copy...

=SUMPRODUCT(LEN($A$2:$A$6)-LEN(SUBSTITUTE($A$2:$A$6,C2,"")))/LEN(C2)

By the way, in future, please post your data as text or use the add-in xl2BB. Then people here can easily copy and paste the data onto their worksheet in order to come up with a solution and test.

Hope this helps, cheers!

 

[AnnaBanana4]

Enter the following formula in D2, and copy...

=SUMPRODUCT(LEN($A$2:$A$6)-LEN(SUBSTITUTE($A$2:$A$6,C2,"")))/LEN(C2)

By the way, in future, please post your data as text or use the add-in xl2BB. Then people here can easily copy and paste the data onto their worksheet in order to come up with a solution and test.

Hope this helps, cheers!

Thanks! It works!

And, yes, will not be posting pictures of data in the future.

 

[AnnaBanana4]

Ack! Follow up problem.

List	Item	Count
1201 1201 1201 1103 1105 1105 1202 1202 1202 1205 1205	101	2
1201 1201 1201 1101 1101	102	0
	103	1
	201	6
	202	3
	203	0
	1101	2
	1102	0
	1103	1
	1201	6
	1202	3
	1203	0

Is there a way to differentiate between numbers with other numbers within it? For example, above, 1201 is being counted as "201" as well, but I don't want that.

Thanks!

 

[Domenic]

In that case, try...

annabanana v2.xlsm
	A	B	C
1	List	Item	Count
2	1201 1201 1201 1103 1105 1105 1202 1202 1202 1205 1205	101	0
3	1201 1201 1201 1101 1101	102	0
4		103	0
5		201	0
6		202	0
7		203	0
8		1101	2
9		1102	0
10		1103	1
11		1201	6
12		1202	3
13		1203	0
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=SUMPRODUCT(--(FILTERXML("<root><item>"&TEXTJOIN("</item><item>",TRUE,SUBSTITUTE($A$2:$A$3," ","</item><item>"))&"</item></root>","//item")=B2))

 

[Domenic]

Although, I think it would be more efficient to define a name with the FILTERXML part of the formula, and then use that name in your worksheet formula. For example, first define the following name (Ribbon >> Formulas >> Name Manager)...

Name:  MyItems

Refers to:  =FILTERXML("<root><item>"&TEXTJOIN("</item><item>",TRUE,SUBSTITUTE($A$2:$A$4," ","</item><item>"))&"</item></root>","//item")

Then enter the following worksheet formula in C2, and copy down:

=SUMPRODUCT(--(MyItems=B2))