Countif

[Mike]

Hello,

Using Windows XP & Excel XP.

I want to keep track of the matching numbers drawn in a lottery to the
numbers on lottery tickets. I have a setup with 5 different tickets that
contain 5 numbers and at the bottom the actual numbers that were drawn. I
want to go ticket by ticket and count how many matches of the actual drawn
numbers. Here's an example.

A B C D E F
-----------------------------------------
1 6 23 38 40 41 0
2 3 11 22 38 45 2
3 15 22 29 44 46 2
4 20 31 37 39 41 0
5 7 9 11 38 39 1
6
7 11 15 29 45 47

I would like to write a formula in F1, F2, F3, F4 & F5 to count the
occurences of the numbers that matches that particular row to the numbers
drawn on row 7.

Thanks in advance,
Michael

[Don Guillett]

where row 5 is the winning numbers and row 6 yours.
=IF(F6=0,"",SUMPRODUCT(COUNTIF(B6:F6,$B$5:$F$5)))

--
Don Guillett
SalesAid Software
[email protected]
"Mike" <[email protected]> wrote in message
news:i2wNd.55274$jn.7128@lakeread06...
> Hello,
>
> Using Windows XP & Excel XP.
>
> I want to keep track of the matching numbers drawn in a lottery to the
> numbers on lottery tickets. I have a setup with 5 different tickets that
> contain 5 numbers and at the bottom the actual numbers that were drawn. I
> want to go ticket by ticket and count how many matches of the actual drawn
> numbers. Here's an example.
>
> A B C D E F
> -----------------------------------------
> 1 6 23 38 40 41 0
> 2 3 11 22 38 45 2
> 3 15 22 29 44 46 2
> 4 20 31 37 39 41 0
> 5 7 9 11 38 39 1
> 6
> 7 11 15 29 45 47
>
> I would like to write a formula in F1, F2, F3, F4 & F5 to count the
> occurences of the numbers that matches that particular row to the numbers
> drawn on row 7.
>
> Thanks in advance,
> Michael
>
>

[Ragdyer]

Try This:

=SUMPRODUCT(COUNTIF(A1:E1,$A$7:$E$7))

Drag down to copy.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Mike" <[email protected]> wrote in message
news:i2wNd.55274$jn.7128@lakeread06...
> Hello,
>
> Using Windows XP & Excel XP.
>
> I want to keep track of the matching numbers drawn in a lottery to the
> numbers on lottery tickets. I have a setup with 5 different tickets that
> contain 5 numbers and at the bottom the actual numbers that were drawn. I
> want to go ticket by ticket and count how many matches of the actual drawn
> numbers. Here's an example.
>
> A B C D E F
> -----------------------------------------
> 1 6 23 38 40 41 0
> 2 3 11 22 38 45 2
> 3 15 22 29 44 46 2
> 4 20 31 37 39 41 0
> 5 7 9 11 38 39 1
> 6
> 7 11 15 29 45 47
>
> I would like to write a formula in F1, F2, F3, F4 & F5 to count the
> occurences of the numbers that matches that particular row to the numbers
> drawn on row 7.
>
> Thanks in advance,
> Michael
>
>

[Mike]

Thanks, it works great.
"Mike" <[email protected]> wrote in message
news:i2wNd.55274$jn.7128@lakeread06...
> Hello,
>
> Using Windows XP & Excel XP.
>
> I want to keep track of the matching numbers drawn in a lottery to the
> numbers on lottery tickets. I have a setup with 5 different tickets that
> contain 5 numbers and at the bottom the actual numbers that were drawn. I
> want to go ticket by ticket and count how many matches of the actual drawn
> numbers. Here's an example.
>
> A B C D E F
> -----------------------------------------
> 1 6 23 38 40 41 0
> 2 3 11 22 38 45 2
> 3 15 22 29 44 46 2
> 4 20 31 37 39 41 0
> 5 7 9 11 38 39 1
> 6
> 7 11 15 29 45 47
>
> I would like to write a formula in F1, F2, F3, F4 & F5 to count the
> occurences of the numbers that matches that particular row to the numbers
> drawn on row 7.
>
> Thanks in advance,
> Michael
>

[Don Guillett]

glad to help

--
Don Guillett
SalesAid Software
[email protected]
"Mike" <[email protected]> wrote in message
news:VFwNd.55282$jn.48965@lakeread06...
> Thanks, it works great.
> "Mike" <[email protected]> wrote in message
> news:i2wNd.55274$jn.7128@lakeread06...
> > Hello,
> >
> > Using Windows XP & Excel XP.
> >
> > I want to keep track of the matching numbers drawn in a lottery to the
> > numbers on lottery tickets. I have a setup with 5 different tickets
that
> > contain 5 numbers and at the bottom the actual numbers that were drawn.
I
> > want to go ticket by ticket and count how many matches of the actual
drawn
> > numbers. Here's an example.
> >
> > A B C D E F
> > -----------------------------------------
> > 1 6 23 38 40 41 0
> > 2 3 11 22 38 45 2
> > 3 15 22 29 44 46 2
> > 4 20 31 37 39 41 0
> > 5 7 9 11 38 39 1
> > 6
> > 7 11 15 29 45 47
> >
> > I would like to write a formula in F1, F2, F3, F4 & F5 to count the
> > occurences of the numbers that matches that particular row to the
numbers
> > drawn on row 7.
> >
> > Thanks in advance,
> > Michael
> >
>
>