This post is part of my Google Sheets function guide. Let’s learn the usage of the SUMSQ function in Google Sheets.

The SUMSQ is a math function in Google Sheets. The purpose of this function is to return the sum of squares of a given series of values/numbers.

In this post, you will get to know the usage of the SUMSQ function in detail. Further, there are alternatives to this function mostly combination formulas.

The conditional sum of squares is also addressed. For this, the SUMSQ doesn’t have any parameters. So it would be a workaround formula.

SUMSQ Function in Google Sheets – Syntax, Arguments, and Examples

Syntax:

SUMSQ(value1, [value2, ...])

Arguments (Parameters) in Detail:

value1 – The first number or array whose squares to add together.

value2, … – Additional numbers or arrays whose squares to add to the square of the first argument.

Please note that ‘value1’ is required, but the subsequent arguments are optional.

Examples:

I have only used the parameter ‘value1’ in the below formula.

=sumsq(5)

The above SUMSQ formula would return 25 which is the square of the number 5. That means if there is only one number within the SUMSQ, which would be equal to value1^2.

Here is one more examples with value1, value2, and value3 as the parameters.

=sumsq(5,5,2)

It is equal to 5*5+5*5+2*2 or 5^2+5^2+2^2.

Note:

In the SUMSQ function in Google Sheets, the arguments (parameters) can either be numbers, cell/array references, or named ranges.

You have already seen how to use ‘numbers’ as argument(s) within the formula. Here are examples of the use of named ranges and cell references within the formula.

In this type of usage, text values, and Boolean values (TRUE/FALSE) will be ignored.

Further, any formula error in any cell within the reference area (range) or named ranges area (range) would cause the SUMSQ formula to return a corresponding error value.

Is there any limitation of using number of arguments?

As per the official documentation, the SUMSQ function in Google Sheets supports an arbitrary number of arguments even though the specified number of arguments is a maximum of 30.

Calculate the Sum of Squares Using Different Formulas in Google Sheets

Sample Data and Sum of Squares Formulas:

=sumsq(A1:B3)

To calculate the sum of squares of a series of numbers, a dedicated function as above is not required in Google Sheets.

Here are those alternatives using POWER, POW, ^ (Caret), and SUMPRODUCT.

Alternatives:

POWER();

=ArrayFormula(sum(power(N(A1:B3),2)))

POW();

=ArrayFormula(sum(pow(N(A1:B3),2)))

Caret;

=ArrayFormula(sum(N(A1:B3)^2))

SUMPRODUCT()

=sumproduct(N(A1:B3)^2)

At one instance these alternatives will return a different output compared to SUMSQ. That’s with the Boolean value TRUE.

The above formulas treat TRUE as 1 whereas the SUMSQ ignores it. That’s why I’ve introduced these alternatives to you.

If we want we can make the above alternative formulas to ignore the TRUE. But I’m not attempting the same as there is no use of it.

Is there a SUMSQIF or SUMSQIFS Function in Google Sheets?

At present, there are no such functions available. I believe there won’t be such functions in the future too. Why?

We can get a conditional sum of the square of values as below using the FILTER function within the SUMSQ.

Example:

In my example, column A contains a list of numbers and column B contains “Yes” or “No”.

I want the sum of squares of the numbers in column A if the corresponding column B values are “Yes”.

=sumsq(filter(A1:A,B1:B="Yes"))

Using the QUERY function also we can do a conditional SUMSQ in Google Sheets. Here is the solution to the above same problem but using QUERY.

=sumsq(query(A1:B,"Select A where B='Yes'"))

If you know these two functions (FILTER/QUERY), it’s just a child’s play to do conditional sum of squares in Google Sheets with single as well as multiple conditions.

That’s all for now. Enjoy!