How to Sum Multiple Rows in Google Sheets (3 Ways)

Today we will look at how to sum multiple rows in Google Sheets. While one method is expectedly common, the others may prove to help in some dynamic datasets.

Let’s get started.

3 Approaches to Sum Multiple Rows in Google Sheets

1. Using the SUM function

The first approach we look at is the most common and simplest in our article, which is summing rows using the SUM function of Google Sheets.

The SUM function syntax:

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

We will use the function in the following dataset to find the total monthly sales of each area.

dataset to sum multiple rows in google sheets

Simply open the SUM function in the target cell and apply the range of the row:


using the sum function on a row of data

Note: With the SUM function being a common formula, Google Sheets should be suggesting the range to its users by default. It is important to be careful and check if the suggested range is what you want.

Use the fill handle to apply the formula to the rest of the column:

sum multiple rows in google sheets using the sum function

This is a tried and tested approach, and you can’t really go wrong using it. However, the simplest approach might not always be the right approach. The rest of the article discusses some niche approaches that can be used to sum multiple rows in Google Sheets.

2. Using a Query to Sum Multiple Rows in Google Sheets

Our first uncommon approach is led by the QUERY function. If you don’t already know, the QUERY function works much like a query language used to work in larger databases.

The QUERY function syntax:

QUERY(data, query, [headers])

Our formula:

=QUERY(B3:E6,"Select C+D+E label C+D+E'Total'",1)

sum multiple rows in google sheets using query

The first thing you notice from the formula is how simple it is since the base of it is centered around the addition of cells. For example, for the first row, we perform C4+D4+E4.

Now, with the QUERY function, we do the same but for the entire columns of C, D, and E at once and present our results as an array. Thus, you can see that we have also labeled our header “Total”.

However, this formula isn’t fully dynamic. Meaning, if I were to add a new column of data in between the existing values, we won’t get the proper results. This is because we have specifically mentioned which columns we are taking into account.

Where the QUERY formula does shine is its ability to extract multiple columns along with the desired result.

Let’s say we want to also extract the Location info with our total. Our updated formula:

=QUERY(B3:E6,"Select C+D+E,B label C+D+E'Total'",1)

extracting multiple columns along with total using query

As long as our QUERY range allows, we can query any form of information from within it.

3. Using Matrix Multiplication

Finally, we will see perhaps the most dynamic formula in our article today and it is centered around the MMULT function.

Let’s see our formula in action first:


using matrix multiplication to sum multiple rows in google sheets

What makes this formula dynamic is that no matter how many rows or columns you add in between the range, the formula will either update itself or add the values accordingly.

When we add a new column to the table, the formula updates to accommodate:

inserting a new column simply updates the formula

When we update or add new entries, the results also update:

the dynamic formula works with new entries animated

Final Words

While the SUM formula may be the go-to method to accomplish summing multiple rows in Google Sheets, the other two can still prove to be powerful additions to your arsenal.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply