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