Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

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.

share|improve this question

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)
share|improve this answer
+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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .