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
1	Student	Correct Answers	Student	Student Answer	Correct Answer
2	Bill	4	Bill	a	a
3	Amy	5	Bill	b	c
4			Amy	c	c
5			Bill	d	d
6			Bill	e	f
7			Amy	a	a
8			Amy	b	b
9			Bill	f	f
10			Amy	d	a
11			Amy	e	e
12			Amy	f	f
13			Bill	c	c

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

Sheet9​

Worksheet Formulas Cell Formula 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]

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!