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

 

[brian.wethington]

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

Could you give more information as to what you did exactly, what is not working about it....?

 

[drc2265]

I went on Excel, right clicked the Tab "sheet2" then clicked View Code, then pasted the code in there....... The Total values were not coming up...

 

[brian.wethington]

You did not insert a module....

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.

Just replace view source with view code in these instructions.

 

[drc2265]

I have no idea why this isnt working........ step by step:
1. Going into excel
2. Open workbook
3. Right Click Tab "Sheet 2"
4. View Code
5. (IN MVB) Clicking Insert, then Module
6. Paste Code
7. X out of MVB Code
8. X out of MVB

Thats what Im doing, and on the Total Columns, each row says "# N/A"...... Am I doing this wrong or what?

=SUM(IF($A$2:$M$2 = $B4:$G4, 1, 0))
Is that the correct code for this??

Thanks for your patience!!

 

[brian.wethington]

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.

You will no longer be using a function Excel created but a User Defined Function (UDF) and you call that function in your sheet by using the "=" then the name of the function and then the "(" followed by the arguments, in this case the 2 ranges, and the ")"

Thus

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

You will probably have to adjust the ranges for your needs though.

 

[drc2265]

This is what I'm seeing after I put in the code and data...?

 

[brian.wethington]

Unfortunately I am not seeing what you are seeing. Could you perhaps post using Colo's HTML Maker? See link at bottom of page!

 

[drc2265]

 

[drc2265]

 

[drc2265]

Sorry, pushed Submit 2 times by accident.