Count Help

[drc2265]

I need to know if there is a formula to count how many cells are the same between to columns, IE: If "Bob" had #s 2, 3, and 5 and the correct numbers are #s 1,3, and 5, Is there a formula to put in the cell below "total" to figure out how many of the #s Bob has are the same from the correct #s?


BOB-----CORRECT-----TOTAL
2-------------1-------------2 (Bob picked #3 and 5 which are correct)
3-------------3
5-------------5

PETE-----CORRECT-----TOTAL
1-------------2--------------1 (Pete picked #6 which is the only 1 correct)
3-------------4
6-------------6

THANKS A LOT!!!!

 

[drc2265]

Thanks a lot Brian and others, you helped a lot!

 

[brian.wethington]

I didnt try Barry's, I figured if you couldnt do it, then there is no way i can do it lol

LOL, well I would not go that far, I am really more of a VBA solutions guy than a formula guy. I just happent to stumble my way through formulas sometimes.

 

[drc2265]

Another question:
PLAYER----------- Numbers ------------------- TOTAL
----------1 3 5 7 9 11 13 15 17 19 21 23 25 27
Player 1-- 13 16 21 24 25 28 -----0 (0 because he got at least one wrong so he is out.)
Player 2-- 2 7 9 11 13 15 -----0 (0 because he got at least one wrong so he is out.)
Player 3-- 5 11 17 21 23 27 -----1 (1 because he got all of his 6 numbers right)

All winning numbers are in bold, losing numbers are not in bold.

Need help with the formula for this problem, don't know how to compare player ones picks to all of the winning numbers, If they get all of their numbers right then they get a 1, if they get 1-6 numbers wrong, they get a 0.

I tried using =SUM(IF($C3:$O3 = $C3:$O3, 1, 0)) but it didn't work correctly, it only compares the cells in the same column, not in other columns. Any help is greatly appreciated. Thanks again.

 

[drc2265]

Another question:
PLAYER----------- Numbers ------------------- TOTAL
----------1 3 5 7 9 11 13 15 17 19 21 23 25 27
Player 1-- 13 16 21 24 25 28 -----0 (0 because he got at least one wrong so he is out.)
Player 2-- 2 7 9 11 13 15 -----0 (0 because he got at least one wrong so he is out.)
Player 3-- 5 11 17 21 23 27 -----1 (1 because he got all of his 6 numbers right)

All winning numbers are in bold, losing numbers are not in bold.

Need help with the formula for this problem, don't know how to compare player ones picks to all of the winning numbers, If they get all of their numbers right then they get a 1, if they get 1-6 numbers wrong, they get a 0.

I tried using =SUM(IF($C3:$O3 = $C3:$O3, 1, 0)) but it didn't work correctly, it only compares the cells in the same column, not in other columns. Any help is greatly appreciated. Thanks again.

 

[drc2265]

Another question:
PLAYER----------- Numbers ------------------- TOTAL
----------1 3 5 7 9 11 13 15 17 19 21 23 25 27
Player 1-- 13 16 21 24 25 28 -----0 (0 because he got at least one wrong so he is out.)
Player 2-- 2 7 9 11 13 15 -----0 (0 because he got at least one wrong so he is out.)
Player 3-- 5 11 17 21 23 27 -----1 (1 because he got all of his 6 numbers right)

All winning numbers are in bold, losing numbers are not in bold.

Need help with the formula for this problem, don't know how to compare player ones picks to all of the winning numbers, If they get all of their numbers right then they get a 1, if they get 1-6 numbers wrong, they get a 0.

I tried using =SUM(IF($C3:$O3 = $C3:$O3, 1, 0)) but it didn't work correctly, it only compares the cells in the same column, not in other columns. Any help is greatly appreciated. Thanks again.

 

[drc2265]

Another question:
PLAYER----------- Numbers ------------------- TOTAL
----------1 3 5 7 9 11 13 15 17 19 21 23 25 27
Player 1-- 13 16 21 24 25 28 -----0 (0 because he got at least one wrong so he is out.)
Player 2-- 2 7 9 11 13 15 -----0 (0 because he got at least one wrong so he is out.)
Player 3-- 5 11 17 21 23 27 -----1 (1 because he got all of his 6 numbers right)

All winning numbers are in bold, losing numbers are not in bold.

Need help with the formula for this problem, don't know how to compare player ones picks to all of the winning numbers, If they get all of their numbers right then they get a 1, if they get 1-6 numbers wrong, they get a 0.

I tried using =SUM(IF($C3:$O3 = $C3:$O3, 1, 0)) but it didn't work correctly, it only compares the cells in the same column, not in other columns. Any help is greatly appreciated. Thanks again.

 

[brian.wethington]

Is a UDF an option for you. This I am sure is doable with a native function in Excel, but as I said before I am not a Formula guru. Thus, waited for a formula guy to come along, but in the process of waiting I threw together a simple UDF that I belleve would work for this purpose.

Function Comp2(Rng1 As Range, Rng2 As Range) As Boolean
'-----------------------------------------------------
'- Rng1 is the range of the values you wish to check
'- Rng2 is the range you want to look in
'- Example: if any of the values in Rng1 are not in Rng2 then you will get False as an answer
'-----------------------------------------------------
Dim Ce As Range
Comp2 = True
For Each Ce In Rng1
    If WorksheetFunction.CountIf(Rng2, Ce) = 0 Then
        Comp2 = False
        Exit For
    End If
Next
        
End Function

I am unsure of your familiarity with UDF's so to use this go into the workbook you wish to use this function in and right click a sheet tab, click view source and in the book you are wanting to use the function right click one of the sheets listed mouse over insert selecting module. Paste the code here.

To use the UDF, go into the sheet you wish to use and type in just like you would a normal function ie

=Comp2(A3:F3, $A$1:$O$1)

This would compare items row 3 to the items in row 1. If there is a number in row 3 that is not in row 1 then you will get False, otherwise the function returns True.

Hope this is helpful!

 

[drc2265]

Hmm, I take it you are using the latest version of Excel because when I right click on the tab, it doesnt say view source , just view code...... not sure what to do now.. thanks for your effort though.

 

[brian.wethington]

Sorry a brain fart... you do click on view code... I was thinking of the wrong application.

 

[drc2265]

Sorry can't get it to work, must be doing something wrong whether its the formula (most likely), =(.....