I have attached a spreadsheet that I created on Google Docs and exported to Excel. I used conditional formatting to color cells green or red, depending on whether or not it matched the answers in each cell of the top row. Green = correct, Red = wrong. My hope was to create a formula that would add all green cells in a row and tell me how many correct out of 19 questions a student in that row received. Ideally, I'd also like to create a formula in each column that told me how many students got that particular question wrong - but that's secondary to my need for a formula in each row.
I have created a Macro to add cell colors, and created a formula to tally them. Unfortunately, every time I use the formula, it tabulates incorrectly. I have gone so far as clearing all conditional formatting rules and recreating them to make sure I did it correctly, and still I get weird results. For instance, there are 19 questions. The first student got 4 wrong, so the end result should be a 15. However, the student received a 17 according to the spreadsheet. What am I doing wrong!?!?
*All names have been removed from the spreadsheet.*
Using this formula instead of what I had works. I don't even need the Macro. Made more work than I needed to for this problem. Thanks for your help!
=SUMPRODUCT(--(G2:Y2=$G$2:$Y$2)) - Values have been changed because my actual spreadsheet has cells that aren't hidden on this.
Bookmarks