Counting cells containing only numbersClosed

Question

Hi all, counting cell containing only numbers

Is there a formula to count only cells containing any numbers in general? Moreover, is there a formula to count values containing an specific number of digits?

Much appreciated

ac3mark · Answer

Ok, So give this a try.  at least it is a good beginning:

=COUNTIF(C2:C15,"?")

The "?" denotes any character, as in:

 

Or a specific number:

 

but to just count them would be:

"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon

jutulu · Answer

Thank you for your reply. However, what I mean, is a formula to count cells that contains only numbers, so that if a letter appears, it would not count it.
It would count 1111, but it wouldn't count 1111A. Moreover, is there a formula that only count cells containing 5 digits, any given 5 digits?

Sorry for the late reply

ac3mark · Answer

That is what the last example does.  Look at the Three Examples.  They are all different ways of finding out what type of "data" is in the cell.  If you wish to find a single  length character string, then use the first.  If you make 2?'s in the first example, then it would find a two character string.  Then, in the Second example, if finds a specific character, in this example, it is the integer of 4.  Then, to answer your question, again, would be the last example, or the function of =COUNT(WHATRANGE).

Whatrange=C2:C11;

nan="NOT A NUMBER"

I hope that this helps!

jutulu · Answer

Thanks Mark for your extensive explanation. My problem with dealing with the COUNT statement is that, when it comes to count columns that contain formulas, the COUNT statement doesn't always work so well and it tends to drive nuts because at some instance it just counts an empty cell for no reason and I guess it is because the cells to count contain formula. That's why I thought if there was a formula that was counting cells with only an specific amount of number numbers it would give me more play to combine it with other statements like AND(OR and get around it and avoid those instances in which it is counting an empty cell. I am probably not the only one with issues like this. It's very annoying when it happens. I have found ways around it, but I usually end up with ridiculously long formulas to close gaps and avoid counting empty cells

Counting cells containing only numbers

4 replies

Subscribe To Our Newsletter!