COUNTIF in a range (and parts of cells)
 

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

COUNTIF in a range (and parts of cells)
 

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

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phrank" wrote in message
...
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

COUNTIF in a range (and parts of cells)
 

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

"RagDyer" wrote in message ...
Use B1 to hold your criteria (147),
Then try this:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phrank" wrote in message
...
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

COUNTIF in a range (and parts of cells)
 

Works beautifully! Thank you very much.

Frank

On Mon, 9 Oct 2006 18:42:57 -0700, "RagDyer"
wrote:

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

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