Hello,
having a column in which data is entered like (1 line here denoted 1 cell)

1,14,17
2
3,8
12,14
14,14,17

the usual countif won't work, of course. How do I go about a formula such as
- "give me a count of all occurrences of the number 17 in the given range"?

If these are typed into a single cell then they will be treated as
text values, not numbers. You could use FIND or SEARCH to look for
"17" in each cell, but of course this will also find "173" and "317".

Hope this helps.

Pete

On May 14, 11:14*am, Mac wrote:
Hello,
having a column in which data is entered like (1 line here denoted 1 cell)

1,14,17
2
3,8
12,14
14,14,17

the usual countif won't work, of course. How do I go about a formula such as
- "give me a count of all occurrences of the number 17 in the given range"?

={SUM(LEN("Range")-LEN(SUBSTITUTE("Range","17","")))/LEN("17")} : Array
Function (Ctrl +Shift + Enter)

"Mac" wrote:

Hello,
having a column in which data is entered like (1 line here denoted 1 cell)

1,14,17
2
3,8
12,14
14,14,17

the usual countif won't work, of course. How do I go about a formula such as
- "give me a count of all occurrences of the number 17 in the given range"?

On May 14, 3:14 pm, Mac wrote:
Hello,
having a column in which data is entered like (1 line here denoted 1 cell)

1,14,17
2
3,8
12,14
14,14,17

the usual countif won't work, of course. How do I go about a formula such as
- "give me a count of all occurrences of the number 17 in the given range"?

=COUNTIF(A:A,"*17*")

If the value to be counted will not be repeated within a cell (eg 17,17,17)
Try this:

With A1:A5 containing the following values....
1,14,17
17
117
17,10,20
12,17,55

This formula returns the count of "17"...
B1: =SUM(COUNTIF(A1:A5,{"17,*","17","*,17,*","*,17"}))

Using the sample data, that formula returns: 4
Note: 117 does NOT equal 17

Is that something you can work with?
Post back if you have more questions.
-------------------------------
Regards,

Ron
Microsoft MVP - Excel
(WinXP, Excel 2003)

"Mac" wrote in message
...
Hello,
having a column in which data is entered like (1 line here denoted 1 cell)

1,14,17
2
3,8
12,14
14,14,17

the usual countif won't work, of course. How do I go about a formula such
as
- "give me a count of all occurrences of the number 17 in the given
range"?

=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"17",""))))/2


"Mac" wrote:

Hello,
having a column in which data is entered like (1 line here denoted 1 cell)

1,14,17
2
3,8
12,14
14,14,17

the usual countif won't work, of course. How do I go about a formula such as
- "give me a count of all occurrences of the number 17 in the given range"?

I tried that to get the count of the number 1. It seems to count all the ones.

The formula result was 8. It should be 1.

What am I doing wrong? Here's the formula I used-
=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"1",""))))/1



"Teethless mama" wrote:

=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"17",""))))/2


"Mac" wrote:

Hello,
having a column in which data is entered like (1 line here denoted 1 cell)

1,14,17
2
3,8
12,14
14,14,17

the usual countif won't work, of course. How do I go about a formula such as
- "give me a count of all occurrences of the number 17 in the given range"?