I made a sheet to use as a test checker. Each test covers different domains. I can count all correct answers using SUMPRODUCT, but when I sort items by domain, the formula stops working. What formula should I use instead?
*first picture referring to the total raw score
=ArrayFormula(SUMPRODUCT(($F$1:$BY$1=$F$4:$BY$4)*($F$2:$BY$2=F5:BY5)))
second picture sorted by domains per test
=SUMPRODUCT($DP$1:$EG$1=$DP$4:$EG$4)*(DP2:EG2=DP5:EG5)
thank you! i want to count the scores for each domain. but I cant do it again since it says use ARRAYFORMULA
Answer
You can try this formula using Map:
=SUM(MAP($F$2:$Q$2, F5:Q5, LAMBDA(x, y, IF(x=y, 1, 0))))
For a dynamic formula, you can try this:
=BYROW(F5:Q, LAMBDA(row, IF(COUNTA(row) = 0, "", SUM(MAP($F$2:$Q$2, row, LAMBDA(x, y, IF(x=y, 1, 0)))))))
Sample Output:
| Question # | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8 | Q9 | Q10 | Q11 | Q12 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Answer | E | C | D | H | B | F | C | F | D | K | A | H | |
| Grade level | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8 | Q9 | Q10 | Q11 | Q12 | Total Score |
| a | E | G | D | H | B | F | C | F | D | A | A | H | 10 |
| A | S | A | A | A | A | A | A | A | A | F | F | 0 | |
| E | G | D | H | B | F | C | F | D | A | A | H | 10 | |
| E | 1 |