# Count Help

#### drc2265

##### Board Regular
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### daniels012

##### Well-known Member
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

#### brian.wethington

##### Well-known Member
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.

Nice Brian!!!!

#### barry houdini

##### MrExcel MVP
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<>""))

#### brian.wethington

##### Well-known Member
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.

#### drc2265

##### Board Regular
Thanks to the both of you for your Info, it will help a lot....

#### drc2265

##### Board Regular
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!

#### brian.wethington

##### Well-known Member
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.

#### drc2265

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

Replies
1
Views
175
Replies
1
Views
92
Replies
1
Views
351
Replies
17
Views
337
Replies
8
Views
221

1,190,876
Messages
5,983,355
Members
439,840
Latest member
billy1989

### 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?

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