 # Add X For Each Matching Cell In 2 Ranges

• Hi,

I've got a quiz going out to lots of folk with fifty questions.

Now each user will receive a percentage score on what they got correct.

To do this I'm writing an enormous string of IF statements eg IF(a1=a2,1,0)+IF(b1=b2,1,0)...etc and then dividing by 50.

Can anyone tell me if there's a smarter way to do this?

(ie count two rows adding one where the two values match)

• Re: Tally Quiz Answers Based On Entries

try this

=SUMPRODUCT((A1:AX1=A2:AX2)*1)/50

• Re: Add X For Each Matching Cell In 2 Ranges

Wow! I love these forums!

Now I've got the percentages for each person, another thing that's stumped me is working out the team averages.

I've got a column that has the team name for each row as well, what's the formula word for doing this, I though vlookup, but I may be barking up the wrong tree.

Essentially I just want to say If Column B = "Team A" then add up Column A (where the individual's percentage score is) and then divide by the number of matches found (this last part is the main part I'm struggling with)