Count a number in a range with cells containing more than one numb
 

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.

Count a number in a range with cells containing more than one numb
 

Hi,

I'm not sure if I've had a bit of brain block on this and made it too
complicated but try this array formula

=SUM(LEN(A1:A20))-SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,",","")))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"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.

Count a number in a range with cells containing more than one numb
 

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.

Count a number in a range with cells containing more than one numb
 

Hi,

Try this:

=SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C1,)))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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.

Count a number in a range with cells containing more than one
 

I think I read your question incorrectly, you want the count of individual
numbers. Change the 8 or use a cell ref for the number to sum

=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,"8","")))

Still an array

Mike

"Mike H" wrote:

Hi,

I'm not sure if I've had a bit of brain block on this and made it too
complicated but try this array formula

=SUM(LEN(A1:A20))-SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,",","")))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"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.

Count a number in a range with cells containing more than one numb
 

If I understand what you want correctly, put this formula in a cell and copy
it down for 8 total rows...

=SUMPRODUCT(LEN(A$1:A$100)-LEN(SUBSTITUTE(A$1:A$100,ROW(A1),)))

Change the 100 in the A$100 (make sure to keep the $ signs) to a row number
that covers your range of interest. The first cell will show you a count of
the number 1, the second cell down will show you a count of the number 2,
and so on for the 8 digits you want to count.

--
Rick (MVP - Excel)


"Vermont Pete C" <Vermont Pete wrote in message
...
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.

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.

Count a number in a range with cells containing more than one
 

Thanks Biff.

I missed the 1-8; which was there in my mind before typing in the formula.
So still I believe the same number can repeat in a cell; right?

By the way who is 'Tracey'?

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

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.

Count a number in a range with cells containing more than one
 

So still I believe the same number can repeat in a cell; right?

The OP didn't mention that possibility but it doesn't matter.

1,1,2

You can just look for the individual digit 1. You don't have to be concerned
with numbers like 11, 13, 21 or 101

By the way who is 'Tracey'?

I have no idea.

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Thanks Biff.

I missed the 1-8; which was there in my mind before typing in the formula.
So still I believe the same number can repeat in a cell; right?

By the way who is 'Tracey'?

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

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.

Count a number in a range with cells containing more than one
 

Mike, Thank you ENORMOUSLY !!!! That is indeed exactly what I wanted to do !

Thanks to everyone else who is helping out in this forum !!!!!!!!!!

We are a very small company that makes specialty waffles (sugar waffles) in
ski resorts here in the East and I want to keep track of days of sun (in may
case this is a "1"), rain, snow, ice, ... - So I basically have 7 wether
conditions I want to be able to record (with each condition represented by a
number).

Maybe I can send some packaged waffles as a thank you - because I probably
have no other way to thank you ! You can send a reply with your address and
I'll send you some.

Regardless, thank you very much again !

Peter


"Mike H" wrote:

I think I read your question incorrectly, you want the count of individual
numbers. Change the 8 or use a cell ref for the number to sum

=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,"8","")))

Still an array

Mike

"Mike H" wrote:

Hi,

I'm not sure if I've had a bit of brain block on this and made it too
complicated but try this array formula

=SUM(LEN(A1:A20))-SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,",","")))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"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.

Count a number in a range with cells containing more than one
 

Jacob, Thank you very much as well !!! Very much appreciated, and the offer
to Mike is the same towards you.
Cheers,
Peter



"Jacob Skaria" wrote:

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.

Count a number in a range with cells containing more than one
 

T.
Thank you very much as well !!! I appcreciate it very much, and the offer
to Mike (see my last thread) goes also towards you.
Cheers,
Peter



"T. Valko" wrote:

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.

Count a number in a range with cells containing more than one
 

Thanks for the generous offer but I didn't do anything to deserve it!

Positive feedback is my reward!

--
Biff
Microsoft Excel MVP


"Vermont Pete C" wrote in message
...
T.
Thank you very much as well !!! I appcreciate it very much, and the offer
to Mike (see my last thread) goes also towards you.
Cheers,
Peter



"T. Valko" wrote:

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.