How to use ARRAYFORMULA to count the values of each row

How to use ARRAYFORMULA to count the values of each row

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?
Read more

=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

Reference/s: Byrow , CountA

Enjoyed this article?

Check out more content on our blog or follow us on social media.

Browse more articles