Looking to count matching columns based on criteria in another column

[jeff_miller]

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

 

[jeff_miller]

Here is a link to the sample data I am referring to above since we cannot do a simple copy and paste of a screenshot!
https://www.dropbox.com/s/fdb9q3ug8hw4i29/Sample Book1.xlsx?dl=0

 

[Eric W]

Try this:

	A	B	C	D	E
Student	Correct Answers	Student	Student Answer	Correct Answer
Bill
Amy

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]
[TD="bgcolor: #9BC2E6"]Bill[/TD]
[TD="bgcolor: #9BC2E6"]a[/TD]
[TD="bgcolor: #9BC2E6"]a[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="bgcolor: #9BC2E6"]Bill[/TD]
[TD="bgcolor: #9BC2E6"]b[/TD]
[TD="bgcolor: #9BC2E6"]c[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Amy[/TD]
[TD="bgcolor: #FFFF00"]c[/TD]
[TD="bgcolor: #FFFF00"]c[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #9BC2E6"]Bill[/TD]
[TD="bgcolor: #9BC2E6"]d[/TD]
[TD="bgcolor: #9BC2E6"]d[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #9BC2E6"]Bill[/TD]
[TD="bgcolor: #9BC2E6"]e[/TD]
[TD="bgcolor: #9BC2E6"]f[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Amy[/TD]
[TD="bgcolor: #FFFF00"]a[/TD]
[TD="bgcolor: #FFFF00"]a[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Amy[/TD]
[TD="bgcolor: #FFFF00"]b[/TD]
[TD="bgcolor: #FFFF00"]b[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #9BC2E6"]Bill[/TD]
[TD="bgcolor: #9BC2E6"]f[/TD]
[TD="bgcolor: #9BC2E6"]f[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Amy[/TD]
[TD="bgcolor: #FFFF00"]d[/TD]
[TD="bgcolor: #FFFF00"]a[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Amy[/TD]
[TD="bgcolor: #FFFF00"]e[/TD]
[TD="bgcolor: #FFFF00"]e[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Amy[/TD]
[TD="bgcolor: #FFFF00"]f[/TD]
[TD="bgcolor: #FFFF00"]f[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #9BC2E6"]Bill[/TD]
[TD="bgcolor: #9BC2E6"]c[/TD]
[TD="bgcolor: #9BC2E6"]c[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=SUMPRODUCT(--($C$2:$C$13=A2),--($D$2:$D$13=$E$2:$E$13))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

 

[jeff_miller]

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!

 

[Eric W]

You're welcome!