Question using countif
 

I'm using office 2007, specifically excel. I cannot get the countif function
to accurately count cells containing multiple numbers. For example below is a
column containing both single and multiple numbers. I'm trying to get a count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help would be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

Question using countif
 

Assuming your data is in column A, try =COUNTA(A:A)

Vaya con Dios,
Chuck, CABGx3



"Tim JA" <Tim wrote in message
...
I'm using office 2007, specifically excel. I cannot get the countif
function
to accurately count cells containing multiple numbers. For example below
is a
column containing both single and multiple numbers. I'm trying to get a
count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help would
be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

Question using countif
 

Hi,

Try this

=SUMPRODUCT(LEN(","&$C$5:$C$17&",")-LEN(SUBSTITUTE(","&$C$5:$C$17&",",","&C19&",",","& REPT("
",LEN(C19)-1)&",")))

C5:C17 is the column of numbers. In cell C19, type the number which you
want to count the occurrence of

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" <Tim wrote in message
...
I'm using office 2007, specifically excel. I cannot get the countif
function
to accurately count cells containing multiple numbers. For example below
is a
column containing both single and multiple numbers. I'm trying to get a
count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help would
be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

Question using countif
 

Hi,

Actually the simplest approach would be to use text to columns to segregate
numbers in different columns and then use the countif

=countif(A3:F50,A55)
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" <Tim wrote in message
...
I'm using office 2007, specifically excel. I cannot get the countif
function
to accurately count cells containing multiple numbers. For example below
is a
column containing both single and multiple numbers. I'm trying to get a
count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help would
be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

Question using countif
 

Hi Tim

'If you are looking to count the number of cells containing multiple numbers
then use the below formula
=COUNTIF(A1:A20,"*,*")

'If you are looking to count the number of instances a particular number is
in that range try the below formula.. Cell B1 holds the number to be
searched...For example with your sample data set 5 occures 2 , 13 occures 3
times etc;

=(SUMPRODUCT(LEN("," & SUBSTITUTE(A1:A20,",",",,") & ","))-
SUMPRODUCT(LEN(SUBSTITUTE("," & SUBSTITUTE(A1:A20,",",",,") &
",","," & B1 & ",",))))/(LEN(B1)+2)

--
Jacob (MVP - Excel)


"Tim JA" wrote:

I'm using office 2007, specifically excel. I cannot get the countif function
to accurately count cells containing multiple numbers. For example below is a
column containing both single and multiple numbers. I'm trying to get a count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help would be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

Question using countif
 

The countif formula you provided works for the cells with multiple numbers in
a column... I also need the count for cells containing single numbers. I
probably didn't explain my problem as clearly as I should have... I
appreciate your assistance.

"Jacob Skaria" wrote:

Hi Tim

'If you are looking to count the number of cells containing multiple numbers
then use the below formula
=COUNTIF(A1:A20,"*,*")

'If you are looking to count the number of instances a particular number is
in that range try the below formula.. Cell B1 holds the number to be
searched...For example with your sample data set 5 occures 2 , 13 occures 3
times etc;

=(SUMPRODUCT(LEN("," & SUBSTITUTE(A1:A20,",",",,") & ","))-
SUMPRODUCT(LEN(SUBSTITUTE("," & SUBSTITUTE(A1:A20,",",",,") &
",","," & B1 & ",",))))/(LEN(B1)+2)

--
Jacob (MVP - Excel)


"Tim JA" wrote:

I'm using office 2007, specifically excel. I cannot get the countif function
to accurately count cells containing multiple numbers. For example below is a
column containing both single and multiple numbers. I'm trying to get a count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help would be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

Question using countif
 

If you need to count the occurrence of a specific number, use Ashish's
formula.

If you need to count all the numbers, try this (assumes no spare commas):

=COUNTA($A$1:$A$50)+
SUMPRODUCT(LEN($A$1:$A$50)-LEN(SUBSTITUTE($A$1:$A$50,",","")))





"Tim JA" wrote in message
...
The countif formula you provided works for the cells with multiple numbers
in
a column... I also need the count for cells containing single numbers. I
probably didn't explain my problem as clearly as I should have... I
appreciate your assistance.

"Jacob Skaria" wrote:

Hi Tim

'If you are looking to count the number of cells containing multiple
numbers
then use the below formula
=COUNTIF(A1:A20,"*,*")

'If you are looking to count the number of instances a particular number
is
in that range try the below formula.. Cell B1 holds the number to be
searched...For example with your sample data set 5 occures 2 , 13 occures
3
times etc;

=(SUMPRODUCT(LEN("," & SUBSTITUTE(A1:A20,",",",,") & ","))-
SUMPRODUCT(LEN(SUBSTITUTE("," & SUBSTITUTE(A1:A20,",",",,") &
",","," & B1 & ",",))))/(LEN(B1)+2)

--
Jacob (MVP - Excel)


"Tim JA" wrote:

I'm using office 2007, specifically excel. I cannot get the countif
function
to accurately count cells containing multiple numbers. For example
below is a
column containing both single and multiple numbers. I'm trying to get a
count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help
would be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

Question using countif
 

The formula you provided works for cells containing a single number. How can
I get it to include a specific number in cells with multiple numbers as well?
For example, if I wanted occurrences for the number 2... there's a cell with
just that number by itself and another cell that has both 2 and 6. In this
case, the formula should come up with 2 occurrences...

"Ashish Mathur" wrote:

Hi,

Actually the simplest approach would be to use text to columns to segregate
numbers in different columns and then use the countif

=countif(A3:F50,A55)
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" <Tim wrote in message
...
I'm using office 2007, specifically excel. I cannot get the countif
function
to accurately count cells containing multiple numbers. For example below
is a
column containing both single and multiple numbers. I'm trying to get a
count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help would
be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

.

Question using countif
 

This UDF can be used to count occurences of numbers or text in a range.

Function CountChar(InRange As Range, Letter As String) As Long
Dim rng As Range
For Each rng In InRange
CountChar = CountChar + Len(rng.text) - _
Len(Application.WorksheetFunction.Substitute(UCase (rng.text) _
, UCase(Letter), ""))
Next rng
End Function

=CountChar(A3:A20,"2")

A cell or range contains 26 or 2,6 or 226 or all of those.

The count will be 4


Gord Dibben MS Excel MVP

On Tue, 11 May 2010 15:43:01 -0700, Tim JA
wrote:

The formula you provided works for cells containing a single number. How can
I get it to include a specific number in cells with multiple numbers as well?
For example, if I wanted occurrences for the number 2... there's a cell with
just that number by itself and another cell that has both 2 and 6. In this
case, the formula should come up with 2 occurrences...

"Ashish Mathur" wrote:

Hi,

Actually the simplest approach would be to use text to columns to segregate
numbers in different columns and then use the countif

=countif(A3:F50,A55)
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" <Tim wrote in message
...
I'm using office 2007, specifically excel. I cannot get the countif
function
to accurately count cells containing multiple numbers. For example below
is a
column containing both single and multiple numbers. I'm trying to get a
count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help would
be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

.

Question using countif
 

Hi,

That is exactly what my formula does. I have tried it. It will search for
all occurrences of 2 in the range above (whether appearing in the cell alone
or along with some other number). If there is more than 1 number in one
cell, the numbers have to be segregated by commas (as shown in your initial
post)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" wrote in message
...
The formula you provided works for cells containing a single number. How
can
I get it to include a specific number in cells with multiple numbers as
well?
For example, if I wanted occurrences for the number 2... there's a cell
with
just that number by itself and another cell that has both 2 and 6. In this
case, the formula should come up with 2 occurrences...

"Ashish Mathur" wrote:

Hi,

Actually the simplest approach would be to use text to columns to
segregate
numbers in different columns and then use the countif

=countif(A3:F50,A55)
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" <Tim wrote in message
...
I'm using office 2007, specifically excel. I cannot get the countif
function
to accurately count cells containing multiple numbers. For example
below
is a
column containing both single and multiple numbers. I'm trying to get a
count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help
would
be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

.

Question using countif
 

Thank you.. you were right it does work!!! I intially copied the formula
wrong. Thanks again.

"Ashish Mathur" wrote:

Hi,

That is exactly what my formula does. I have tried it. It will search for
all occurrences of 2 in the range above (whether appearing in the cell alone
or along with some other number). If there is more than 1 number in one
cell, the numbers have to be segregated by commas (as shown in your initial
post)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" wrote in message
...
The formula you provided works for cells containing a single number. How
can
I get it to include a specific number in cells with multiple numbers as
well?
For example, if I wanted occurrences for the number 2... there's a cell
with
just that number by itself and another cell that has both 2 and 6. In this
case, the formula should come up with 2 occurrences...

"Ashish Mathur" wrote:

Hi,

Actually the simplest approach would be to use text to columns to
segregate
numbers in different columns and then use the countif

=countif(A3:F50,A55)
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" <Tim wrote in message
...
I'm using office 2007, specifically excel. I cannot get the countif
function
to accurately count cells containing multiple numbers. For example
below
is a
column containing both single and multiple numbers. I'm trying to get a
count
based on any number in the column. Perhaps, I'm using the wrong count
function. I've tried using wild cards... but to no avail. Your help
would
be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

.

Question using countif
 

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" wrote in message
...
Thank you.. you were right it does work!!! I intially copied the formula
wrong. Thanks again.

"Ashish Mathur" wrote:

Hi,

That is exactly what my formula does. I have tried it. It will search
for
all occurrences of 2 in the range above (whether appearing in the cell
alone
or along with some other number). If there is more than 1 number in one
cell, the numbers have to be segregated by commas (as shown in your
initial
post)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" wrote in message
...
The formula you provided works for cells containing a single number.
How
can
I get it to include a specific number in cells with multiple numbers as
well?
For example, if I wanted occurrences for the number 2... there's a
cell
with
just that number by itself and another cell that has both 2 and 6. In
this
case, the formula should come up with 2 occurrences...

"Ashish Mathur" wrote:

Hi,

Actually the simplest approach would be to use text to columns to
segregate
numbers in different columns and then use the countif

=countif(A3:F50,A55)
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tim JA" <Tim wrote in message
...
I'm using office 2007, specifically excel. I cannot get the countif
function
to accurately count cells containing multiple numbers. For example
below
is a
column containing both single and multiple numbers. I'm trying to
get a
count
based on any number in the column. Perhaps, I'm using the wrong
count
function. I've tried using wild cards... but to no avail. Your help
would
be
greatly appreciated.



5
9
2,6
2
3,6
6,7
5,13
2

6

1,10,13
13

.