Looking to count matching columns based on criteria in another column

jeff_miller

New Member
Joined
Apr 7, 2005
Messages
44
Hello I have tried to simplify my example as much as possible.

I have a Column C containing student names. In Column D is the answer that student chose on a test, and in Column E, I have the correct answers to these questions.

In A2 and A3 I have the students names
In B2 and B3 I would like to have the count of the number of correct answers that each of the students have.
In my attached spreadsheet I have the correct result in B2 and B3

My actual data is much more complex than this so I am looking for a formula that I can place in B2 and B3 that will give me the correct answer without creating additional formulas in other cells.

Thank YOU!!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,550
Try this:

ABCDE
1StudentCorrect AnswersStudentStudent AnswerCorrect Answer
2Bill4Billaa
3Amy5Billbc
4Amycc
5Billdd
6Billef
7Amyaa
8Amybb
9Billff
10Amyda
11Amyee
12Amyff
13Billcc

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet9

Worksheet Formulas
CellFormula
B2=SUMPRODUCT(--($C$2:$C$13=A2),--($D$2:$D$13=$E$2:$E$13))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



You can add a screenshot like I did by using one of the special tools, like the HTML Maker in my signature.
 

jeff_miller

New Member
Joined
Apr 7, 2005
Messages
44
Thanks Eric W!!!!
I believe I can adapt that on my actual Data.
I was using this formula, but couldn't figure out the $C$2:$C$13=A2 part for some reason.
Appreciate the help!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,368
Messages
5,444,053
Members
405,264
Latest member
JohnP1972

This Week's Hot Topics

Top