How to count on multiple sheets of Google Sheets

To calculate totals on Excel or sum up on Google Sheets, we will use the SUM function. However, you will often encounter aggregate cases with data areas in the same data sheet. So what if you want to sum the data areas in many different sheets? Is it possible to use the SUM function? The following article will guide you how to count on multiple sheets of Google Sheets.

How to use SUMPRODUCT function in Excel
How to combine Sumif and Vlookup functions in Excel
How to use DSUM function in Excel

For example, we will deal with the data table below. The data for 3 sheets is 2014, 2015 and 2016. The total number of students in grade A1 over 3 years is calculated and the total number of students in all grades is calculated for 3 years.

How to count on multiple sheets of Google Sheets Picture 1

1. Calculate the sum of 1 cell on multiple Google Sheets sheets

Step 1:

The data table structure of the number of students in the sheets will be the same. The number of students in grade A1 will be in cell B2 and the other sheets will be in that position.

How to count on multiple sheets of Google Sheets Picture 2

Step 2:

In the last sheet Total to enter the result of the total number of students in A1 for 3 years, in the result box you enter the formula = SUM ('2014'! B2; '2015'! B2; '2016'! B2) and then press Enter. In which 2014, 2015, 2016 are the names of sheets and B2 that contain the data to be summed.

You will immediately see the result displayed as 67 as shown.

How to count on multiple sheets of Google Sheets Picture 3

Step 3:

To enter the results for the remaining cells just drag the first result box down to the other cells.

How to count on multiple sheets of Google Sheets Picture 4

2. Calculate a total area on multiple Google sheets sheets

We will calculate the total number of students in each grade year by year. That means you need to calculate the total number of students in 2014, 2015 and 2016 to produce the number of students in 3 years.

In the data table, the student data area for each year will be B2: B5.

Step 1:

In the result input box, calculate the total number of students in grades over 3 years, enter the formula = SUM ('2014'! B2: B5; '2015'! B2: B5; '2016'! B2: B5) and click Enter. In 2014, 2015 and 2016 are still data sheets. B2: B5 will be the data area to be summed.

How to count on multiple sheets of Google Sheets Picture 5

Step 2:

The final result will show the data as shown below.

How to count on multiple sheets of Google Sheets Picture 6

Thus, summing a cell or region on different sheets of a Google Sheets file will also use the SUM function. Usage is the same as when you calculate multiple cells with a sheet, but need to insert the sheet name.

Video tutorial for totaling on Google Sheets

See more:

How to insert checkboxes on Google Sheets
6 useful functions in Google Sheets you may not know yet
How to enter 0 in Google Sheets

I wish you all success!

calculate total Google Sheets

Isabella Humphrey

Update 25 May 2019

You should read it

May be interested

How to enter 0 in Google Sheets
normally when entering 0 on google sheets, it will automatically disappear if that 0 is in front of the number line. so how to re-display 0 in google sheets.
How to create a phone number can be called on Google Sheets
google sheets has a call feature when entering phone numbers into data tables, via callers installed on the computer.
How to view editing history on Google Sheets
google sheets will automatically save the content edits on the file so that users can review it when needed, or restore the modified version.
6 useful functions in Google Sheets you may not know yet
in this article, i will show you 6 useful functions for the google sheets spreadsheet that you should know such as vlookup, countif, sumif, join, index, ... in addition, you can refer to the functions. and other tips follow the link in the post.
How to fix Google Sheets not allowing scrolling
google sheets is a free and easy to use spreadsheet application that works right out of the box. however, sometimes users face some glitches.
How to set up the right to edit spreadsheets on Google Sheets
when working on google sheets online, if you want to restrict editing of certain column or row data areas, you can set editing rights on google sheets.
How to quickly delete multiple sheets in Excel
working with sheets in excel involves the very basics of working with excel tables. with an excel sheet we have operations like adding sheets, changing sheet colors or deleting sheets when not in use.
How to write notes, comments on Google Sheets
on google sheets has a comment writing feature, notes in the data box can help users understand the content while working online with many people.
How to insert checkboxes on Google Sheets
adding checkboxes to optional list data on google sheets makes it easier for users to manage content.
5 Google Sheets features you should know
you may be familiar with the basic concepts of google sheets, but google spreadsheets have many great features you may not know. this article will introduce you to 5 useful features of google sheets.