How to Sum Cells from Different Sheets in Google Sheets (3 Easy Ways)

In this simple tutorial, we will look at a few ways we can use to sum cells across different sheets in Google Sheets.

While the idea is simple, proper cell referencing plays a huge part as we use different functions for different scenarios.

Let’s get started.

3 Scenarios to Sum Cells from Different Sheets in Google Sheets

We will use the following datasets spread across multiple worksheets to show our examples. Each contains monthly Sales data for January, February, and March.

For January:

january worksheet - sum cells from different sheets in google sheets

For February:

february worksheet

And finally, for March:

march worksheet

Now, we will discuss three separate scenarios where we use these datasets to sum values in Google Sheets.

1. The Basic Sum of Cells Across Multiple Sheets in the Same Google Sheets Spreadsheet

Let’s go to our fourth worksheet that contains (for now) a lone field that wants the total revenue accrued across the months.

simple worksheet to calculate the total revenue in

This essentially means that we have to sum the revenue columns of all three months in this field.

And what better way to do this than by using the SUM function?

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

sum function syntax in google sheets

Let’s test it out by summing the total revenue of January:

Step 1: Open the SUM function in a cell, =SUM(.

Step 2: Move to the January worksheet and select the revenue range.

selecting the revenue column from the january worksheet for sum

The cell reference to a different worksheet in Google Sheets is always in this format:




Step 3: Close parentheses and press ENTER.


sum cells from a different sheet in google sheets

More than simplicity, you will notice that the SUM function can take multiple sum ranges as arguments, making it perfect to reference different sheets to sum in Google Sheets.

So, to calculate the total revenue, we simply have to insert the range of each respective month’s revenue ranges in this formula, separated by commas of course:


sum cells from multiple different sheets in google sheets using the sum function

Sum of Cells from Different Spreadsheets in Google Sheets

Being completely browser-based, Google Sheets has a different approach when referencing cells in completely different worksheets. This is done solely on the back of the IMPORTRANGE function.

IMPORTRANGE(spreadsheet_url, range_string)

This function will help reference a cell or a cell range from a different spreadsheet that will be used in the SUM function.

For this example, we’ve placed the Monthly Revenue data in different spreadsheets like this one:

the january revenue data placed in a different spreadsheet

Let’s see how we can sum all the revenues in different spreadsheets:

Step 1: Open the SUM and IMPORTRANGE functions, =SUM(IMPORTRANGE(.

opening the sum and importrange functions in google sheets

Step 2: For the spreadsheet_url, move to the spreadsheet that contains the data (in this case, it is the January Data spreadsheet) and copy the URL. See the image below:

copying the url of a different spreadsheet

Note the worksheet name as well.

Step 3: Paste the URL inside quotation marks (“”) in the spreasheet_url field. The quotation is important.

pasting the irl in the spreadsheet_url field of importrange

Step 4: For the range_string field, simply enter the worksheet cell range (sheet_name!cell_range) inside quotations (“”) as well.

Remember we asked you to note the worksheet name of the dataset? This is where you’ll use it:

applying the range_string data in the importrange function

Step 5: Close parentheses and do the same for the other spreadsheets (February and March). The final formula will look something like this:






sum cells from multiple different spreadsheets in google sheets with sum and importrange

Important Note: When doing this for the first time, the cell may show a REF error. Simply click on the cell and allow permission to access the other spreadsheets. You may have to do this multiple times depending on the number of referenced spreadsheets.

2. Conditionally Sum Cells from Different Sheets in Google Sheets

Summing with conditions or criteria is a common occurrence in a spreadsheet.

Thankfully, Google Sheets provides its users with a quick and easy way to do so and also allows for the conditions and values to be imported from many different worksheets.

And it all revolves around the SUMIF function.

SUMIF(range, criterion, [sum_range])

sumif function syntax in google sheets

While the final field of the function is given as optional, we will still have to utilize it in this situation.

To sum cells from different sheets in Google Sheets is essentially summing with multiple criteria with each worksheet being a different criterion in this case.

And since any state can work regardless of being present or not, this logic takes the form of the OR condition. This means that we can simply add the SUMIF results of each sheet together to get the total.

For example, let’s say we want to find the total revenue of the item “S22” over the three months. The SUMIF functions for each will be:







The final formula is:


using sumif function to sum cells from different sheets in google sheets

We can update the criterion text for each SUMIF function to point to a single cell reference to make the formula more dynamic:


updating the formula with cell references to make it more dynamic

This also gives us a chance to apply a drop-down list to cycle through different items:

using sumif function to sum cells from different sheets in google sheets animated

Alternatively, the SUMIFS function can also be used here, albeit with different argument locations:


sumifs alternative to sum cells from different sheets in google sheets

This is best used when you have multiple criteria in the same dataset (AND logic) to look for.

Learn More: Applying SUMIF from Another Sheet in Google Sheets

3. Sum the Same Cell Across Different Sheets in Google Sheets

For all the different Month worksheets (January, February, and March) the Revenue cell for each item is the same. This gives us the advantage of simply referencing the same cell number for each worksheet in the sum.

For example, cell E2 for the iPhone Revenue for each month.

If you’ve used MS Excel or come from it, you may know that you can use the SUM function with a worksheet range if summing the same cell in each worksheet.


However, this is not possible in Google Sheets.

Forcing us to stick to traditional cell referencing to sum the same cells from different sheets in Google Sheets:


using sum function to sum the same cell across different sheets in google sheets

But, if you really want to replicate Excel’s capability of summing the same cell over different worksheets, you have to employ the use of the addon 3D Reference.

Using 3D Reference Add-on to Sum the Same Cell in Different Worksheets

From the add-ons Marketplace (Extensions > Add-ons > Get add-ons) find and install the 3D References add-on.

the 3d reference add-on in the google sheets marketplace

This will add a new and unique function in Google Sheets called DDDREF.


The function takes the names of the starting and ending worksheets and returns the reference of the given cell or range of cells.

To find the sum of the same cell in different worksheets in Google Sheets, the formula will be:


using the add-on function dddref to sum the same cells across multiple worksheets

Final Words

That concludes all the ways we can use to sum cells from different sheets in Google Sheets.

With functions like SUM and conditional ones like SUMIF and SUMIFS, there really is no simpler way to work around this topic. All you have to do is make sure the referencing data is correct in each case.

Feel free to leave any queries or advice you may have 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