Looking for a formula to count single digits vs. double digits?

Hello All,


Happy New Year!!!


First time in a cite like this. I have been reading some of the posts and love all the help folks are willing to give.



I am looking to add digits that appear in a column and don't seem to understand how to do the array properly.

Let's say I have column L and have information in L10 - L287. I have the following numbers in the columns,

L105 2, 3, 7, 12, 17
L106 3, 5, 8, 11, 12
L107 4, 5, 6, 14, 17

Say that I simply want to count how many times each number appears. In putting in formula's and trying to find out how may times JUST 7 appears, NOT 17, it does not work. It counts the 7 as well as the 17 and gives me a value of 3 times. I would like to be able to have it tell me there is one 7 and two 17's.

The formula I have used is this: =SUM(LEN(L105:L287)-LEN(SUBSTITUTE(L105:L287,"7","")))/LEN("7")

If I do the same formula and use 17, it will pick out just the 17's.

The formula works for double digits but not for single digits?

Clearly I don't understand how this is done. Sorry. I am not the brightest bulb on the tree.

Anyone know how I can do that?

Thank you in advance for any assistance. Have a great evening.

I love groups like this. Makes you realize people are good.

Thank you so much for your reply Ron. I copied your exact formula in my computer and it read a number of 331 ??? That is about 321 too many.

I should clarify that I am keeping stats for a hockey team for some neighborhood kids. Each time they are on the ice, they receive a + when we score and receive a - if we get scored on. To keep track of who was on the ice I simply use the 1, 2, 3, 4, 5 in that column. Each players number that was on the ice is represented and there are normally 5 players on the ice. Sometimes less, but the principle is the same that I use the numbers and separate them by a ,

the number of the players on the team are 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 17 . Those are all of the numbers for the kids on the team.

The formula I used works for 8, 10, 11, 12, 13, 14, 15 and 17. It won't work for the others as the numbers show up in the ones spot for the other players numbers.


You have (" "&A1, 2), " Am I supposed to replace the A1 and use 1? Do I put in &A1, 2, 3, 4, 5, 6, 7, 8, 9, so it works for all single numbers?

I am sorry to be a pain in your a$$ and I truly appreciate your even being willing to respond to this. Thank you so much.

I will keep my eye on this for a while in the hopes that you (or anyone else) will reply.

Again, thanks a million Ron. I will keep my eye out.

JB that one worked great but does NOT count the #7 in a case where it reads 7, 11, 12, 14, 15. Is that because it won't read when 7 is the first numbers? That is what it appears. I counted all fo them and also did the formula with the #6. Same thing happened. Always worked unless the number was the first number.

I simply took and placed a , at the beginning of each cell. ie. , 3, 5, 6, 7, 11 instead of 3, 5, 6, 7, 11. That ended up doing the trick for what I needed. It seems odd that it had to be done that way but so be it.

Thank you both for your assistance on this. I truly appreciate both of your efforts.

Have a wonderful New Year, gentlemen.

Hi when I use the code you provided it works great until I try using it to find "10" it then counts not only "10". but "10.5" as well. Is there an easy fix for this?

ATTACH]823730[/ATTACH]test 1.xlsx