How to count on multiple sheets of Google Sheets

To sum a cell or region on multiple sheets on Google Sheets, users will still use the SUM function.

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 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 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 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 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 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 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.