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

[Vermont Pete C]

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.

 

[Mike H]

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

 

[Jacob Skaria]

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

 

[Shane Devenshire]

Hi,

Try this:

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

 

[Mike H]

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

 

[Rick Rothstein]

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.

 

[T. Valko]

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.

 

[Jacob Skaria]

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

 

[T. Valko]

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.

 

[Vermont Pete C]

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

 

[Vermont Pete C]

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

 

[Vermont Pete C]

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]

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

Positive feedback is my reward!