Prev: sumifs multiple columns help
Next: update vlookup formula sheet reference for multiple sheets
From: Tim JA on 10 May 2010 14:52
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
> >
From: Steve Dunn on 11 May 2010 03:32
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" <TimJA(a)discussions.microsoft.com> wrote in message
news:941FE56C-ECDE-421A-825E-216834536722(a)microsoft.com...
> 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
>> >

From: Tim JA on 11 May 2010 18:43
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 JA(a)discussions.microsoft.com> wrote in message
> news:D88BC3C5-BA45-4860-B487-CA195D327E46(a)microsoft.com...
> > 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
> >
> .
>
From: Gord Dibben on 11 May 2010 19:13
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 <TimJA(a)discussions.microsoft.com>
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 JA(a)discussions.microsoft.com> wrote in message
>> news:D88BC3C5-BA45-4860-B487-CA195D327E46(a)microsoft.com...
>> > 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
>> >
>> .
>>

From: Ashish Mathur on 11 May 2010 20:53
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" <TimJA(a)discussions.microsoft.com> wrote in message
news:71586C06-4028-49A7-8EC7-681551921D4B(a)microsoft.com...
> 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 JA(a)discussions.microsoft.com> wrote in message
>> news:D88BC3C5-BA45-4860-B487-CA195D327E46(a)microsoft.com...
>> > 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
>> >
>> .
>>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: sumifs multiple columns help
Next: update vlookup formula sheet reference for multiple sheets