Sum of Squares Using the SUMSQ Function in Google Sheets

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.

SUMSQ function examples in Google Sheets

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:

AB
1310
241
325
=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!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.