# How do I count the times each number appears in columns of numbers?

I am sure this must be easy, but I am inexperienced. About the best way to think of my problem is to think of it as trying to sort and then count lottery numbers. To stay simple, let's do a Pick 3 game. Let's look at 10 drawings. I would split each drawn number into a separate column:

``````DATE  BALL#1  BALL#2  BALL#3
3/1      1      3      5
3/2      3      7      8
3/3      2      2      1
3/4      5      7      6
3/5      2      3      1
3/6      0      5      9
3/7      3      7      0
3/8      6      8      4
3/9      2      4      3
3/10     7      1      2
``````

I would like to be able to build formulas into cells that would tell me how many times each number appeared overall, and how many times each number appeared in the position it occurred.

Like this (using the above example):

``````Number  Overall Count  Ball#1 Count  Ball#2 Count  Ball#3 Count
0          2              1               0            1
1          4              1               1            2
``````

(That is, The number zero appears twice overall, and came up once as the first number drawn; zero times as the middle ball; and once as the third ball. Likewise, the number 1 was drawn four times in our 10-day period. It was the first ball once, the second ball once and the third ball twice.)

And so on.

All help appreciated. I have access to Excel and Microsoft Works, or of course if there is a Google Docs way to handle this

All thanks for any help.

-

You can use the `=COUNTIF(range, criteria)` function.

Something like

``````Number  Overall Count             Ball#1 Count
0       =COUNTIF(B2:D11,A13)       =COUNTIF(B2:B11,A13)
``````
-
+1, beat me to it – Sathya Mar 6 '11 at 6:19
Thanks! It worked. – Andy C. Mar 9 '11 at 4:39
Follow-up.... Is there a formula to let me count if there are duplicates showing up in a row? If I have 3 to 6 columns of numbers, I'd like to know if numbers duplicated in any particular row? Again -- the best way to think of it is lottery numbers: – Andy C. Mar 9 '11 at 4:40
@Dennis Williamson and @Sathya ... thanks. (See above comment) ...continuing... – Andy C. Mar 9 '11 at 4:41
If it's a row with 7-7-5; then 4-5-2; then 1-5-7; then 2-2-5; then 1-0-9; then 1-1-3; then 1-0-1; then 5-4-2. You can see that the first, fourth, sixth and seventh row had duplicates. Is there a formula that would calculate that for me? – Andy C. Mar 9 '11 at 4:43