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)))

 

[Ragdyer]

Try This:

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

Drag down to copy.

 

[Mike]

Thanks, it works great.

 

[Don Guillett]

glad to help