Count a number in a range with cells containing more than one numb
Numbers are 1 to 8.
Since there won't be any combinations of numbers all you need to do is look
for the individual digits.
--
Biff
Microsoft Excel MVP
"Jacob Skaria" wrote in message
...
Try the below formula
1. Should be Comma separator
2. Number to be searched in cell B1
3. A1:A10 is the range to be searched...
=(SUMPRODUCT(LEN(","&SUBSTITUTE(A1:A10,",",",,")&" ,"))-SUMPRODUCT(LEN(SUBSTITUTE(","&SUBSTITUTE(A1:A10,", ",",,")&",",","&B1&",",""))))/(LEN(B1)+2)
If this post helps click Yes
---------------
Jacob Skaria
"Vermont Pete C" wrote:
I've got a range (one column) where each cell either
- has no value or number in it (empty cell)
- has 1 or more values in it (and I can separate by , ; - / or any other
symbol. )
Cells only have numbers, no text. Numbers are 1 to 8.
I would like to know how many occurances of each number happen in that
range.
Using count function does not work clearly (or any other count function).
For instance:
A1: 1,5
A2:
A3: 8
A4: 2,4,6,7,
etc...
Anyone knows if there is an existing function within excel or needs to be
macro (not my forté)?
Many thanks to the this community...
Peter C.
|