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
|