How to use ARRAYFORMULA to count the values of each row

Question

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

Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. — TheMaster
– TheMaster, Commented Apr 7 at 13:46

Venara · Accepted Answer · 2025-04-07 07:55:47Z

1

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

answered Apr 7 at 7:55

Venara

8332 gold badges4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use ARRAYFORMULA to count the values of each row

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related