COUNTIF in a range (and parts of cells)

[Phrank]

Hello,

I've got a row of numbers as shown below. I need to count the number
of times a particular number shows up in this column range. But, if I
do the usual COUNTIF, I don't get the proper number. I've tried
=COUNTIF(A1:A10,"147"), but only come up with 2. I need an answer of
6. Any help would be greatly appreciated.

147
148
147
147,150
145,146
147,147
142
143,147
140
136

 

[RagDyer]

Use B1 to hold your criteria (147),
Then try this:

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,"")))/LEN(B1)

 

[Epinn]

Hi RD,

Thanks for the formula. I learned something similar the other day - counting "a" in a string. Glad you use SUMPRODUCT instead of SUM and CSE.

The only way that the original poster can use COUNTIF is when he/she does "text to columns" first. I tried it on the data set posted and it worked. Don't know the arrangement of all data to tell if it will work.

Any comments?

Epinn

Use B1 to hold your criteria (147),
Then try this:

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,B1,"")))/LEN(B1)

 

[Phrank]

Works beautifully! Thank you very much.

Frank