Help with if then formula

[trfountain22]

Hello,
I need a formula that will lookup whether or not an answer is correct in a cell and then calculate the total percentage.
I'm attaching the file.
The answers in red are incorrect. The correct answer for that column will not be red. Then I need to total the percentage correct (out of 40 questions). I started doing it by hand but it's taking too long and I have several other teachers that need it done as well. I need a faster way!

[Special-K]

1. "I need a formula that will lookup..."
LOOKUP where? You only have one sheet in the file.

2. "The answers in red are incorrect. The correct answer for that column will not be red."
The cells have been manually coloured.
The only way to determine the colour of a cell (unless it's set by Condtional Formatting - in which case just use the same conditions) is by VBA, formulas can't do this.

[trfountain22]

I'm sorry I was confusing. The color is really irrelevant. It just shows me what answers are correct. I added a sheet with the correct answers. See if it makes more sense now.

[Special-K]

There's probably another way

=SUMPRODUCT(--(B7:AO7=Sheet2!A$2:AN$2))/40*100
and copy down the column

[trfountain22]

Thanks so much!

[FDibbins]

Thanks so much!

welcome to the forum

For future reference, please take a moment to read the forum rules and use thread titles that are descriptive of your problem - not what you think the answer might be. (think google search terms?).

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?