Prev: sumifs multiple columns help
Next: update vlookup formula sheet reference for multiple sheets
From: Tim JA Tim on 7 May 2010 18:51
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: CLR on 7 May 2010 19:34
Assuming your data is in column A, try =COUNTA(A:A)

Vaya con Dios,
Chuck, CABGx3



"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 8 May 2010 03:28
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 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 8 May 2010 03:33
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: Jacob Skaria on 8 May 2010 03:36
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
>
 |  Next  |  Last
Pages: 1 2 3
Prev: sumifs multiple columns help
Next: update vlookup formula sheet reference for multiple sheets