Countif - extended functionality

[Mac]

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"?

 

[Pete_UK]

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

 

[minli]

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

 

[shaqil]

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*")

 

[Ron Coderre]

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)

 

[Teethless mama]

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

 

[alstubna]

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