Count Values contained in a range

[uk747]

Hi

I know you can coun text in a range using wildcards
is it possible with Values and a value in a cell reference

Want to know how to count how many times 123 appear in range below

and if 123 was in F2 how many times F2 appeared in range

123​

876​

5123​

41235​

 

[ExceLoki]

something like this?

Book1
	A	B	C	D	E	F
1	123		count
2	876		4	1		123123123
3	5123
4	41235
5	123123123
Sheet1

Cell Formulas
Range	Formula
C2	C2	=SUM(IFERROR(SEARCH("*123*",A1:A5),0))
D2	D2	=IF(SEARCH(123,F2),SUM(IFERROR(SEARCH(F2,A1:A5),0)),0)

 

[uk747]

Thanks

=SUM(IFERROR(SEARCH("*123*",A1:A5),0)) Worked for hardcoded values

Tried =SUM(IFERROR(SEARCH(F2, A1:A5),0)) where F2 contained 123 and 123123123 was counted as 1, 41235 counted as 2 and 5123 counted as 2

 

[uk747]

PS Also need it to count where the values in A2:A5 are text or numbers

e.g. If F2 has 123 and values in A2:A5 are below the result should be 4 because value in F2 which is 123 appears in 4 of the cells

123​

4123​

666​

6123d

41231234​

 

[Peter_SSs]

Try ..

=COUNT(SEARCH(F2,A1:A5))

 

[uk747]

Try ..

=COUNT(SEARCH(F2,A1:A5))

Thanks Peter a lot better than the one i eventually got(didn't realise there was a COUNT Function)
=SUM(IF(ISNUMBER(SEARCH(TEXT(F2, "0"), A1:A10)),1,0))

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.