Excel: How can I sum the squares of values in a range meeting a criteria?

Solution 1:

I would use SUMPRODUCT():

=SUMPRODUCT((--(A:A="dog")*(B:B))^2)

for this case:

enter image description here

Solution 2:

you can also use this array formula:

=SUMSQ(IF(A1:A10="dog",B1:B10))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Also, being an array formula one should avoid full column references.

enter image description here

Excel: How can I sum the squares of values in a range meeting a criteria?

Solution 1:

Solution 2:

Related

Recent Posts