Count Help

drc2265

Board Regular
Joined
Jul 30, 2007
Messages
96
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!!!!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
can you just simply add a column?
In a third column put this formula all the way down.
=A2=B12
Then in your total column you could have =countif(C2:C4,true)

Michael
 
Upvote 0
Assuming your data started in A1 to A3 and B1 to B3

=SUM(IF(A1:A3 = B1:B3, 1, 0))

Entered with CSE

Edit, Thought I should define a bit better what I mean by entered with CSE.

CSE means Ctrl + Shift + Enter, not just enter.
 
Upvote 0
You could also use SUMPRODUCT which only requires ENTER

=SUMPRODUCT(--(A1:A10=B1:B10))

although this will count rows which are blank so you might want

=SUMPRODUCT((A1:A10=B1:B10)*(A1:A10<>""))
 
Upvote 0
You could also use SUMPRODUCT which only requires ENTER

=SUMPRODUCT(--(A1:A10=B1:B10))

although this will count rows which are blank so you might want

=SUMPRODUCT((A1:A10=B1:B10)*(A1:A10<>""))

Hmm.. I tried that, but I must have done something wrong... I thought it should work but I did not know so I went with what I knew. :)
 
Upvote 0
Little More Help

#------------Name-------1 vs. 2------3 vs. 4-----5 vs. 6
1----------Player 1----------2-------------3-----------6
2----------Player 2----------1-------------4-----------6

(#'s in BOLD are the ones correct).......

In the total column to the right of "5 vs 6", for player 1, i have:
player 1: {=SUM(IF($C3:$O3 = $C2:$O2, 1, 0))}
player 2: {=SUM(IF($C4:$O4 = $C2:$O2, 1, 0))}
The total comes out correct but I cannot fill it down because the second C and O values have to remain C2 and O2 because thats where the correct data is, If i fill it down, then it will look like this:
player 3: {=SUM(IF($C5:$O5 = $C3:$O3, 1, 0))}
-------------------------------------------^

Thanks for all of your help with this but dont want to write out 220 formulas lol

Thanks again guys!
 
Upvote 0
It looks like to me you need a completely absolute for the second

=Sum(If($C3:$O3 = $C$2:$O$2, 1, 0))

Confirmed with CSE

However, were you not able to get Barry's Formula to work... that might be an easier route to take.
 
Upvote 0
I didnt try Barry's, I figured if you couldnt do it, then there is no way i can do it lol
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top