Sum the Total of Same Cell on All Worksheets in Excel

»»»Sum the Total of Same Cell on All Worksheets in Excel

In Microsoft Excel spreadsheet software, there may be a need to sum the total of a cell in all individual worksheets to display in a master sheet.

For example, you have 12 worksheets that contain sales figure for every months in the year in cell T1, with worksheets named as per the month, i.e. January, February, March and so on.

If you want to adds the total of every month’s sales figure in another worksheet to display the total sales for the year, there are several ways to do so.

Method 1: Manually Type or Select the Cells

You can easily add the total of all cells in different sheets by manually typing or selecting the cells, which will result in the following formula:

='January'!T1+'February'!T1+'March'!T1+'April'!T1+'May'!T1+'June'!T1+'July'!T1+'August'!T1+'September'!T1+'October'!T1+'November'!T1+'December'!T1

Method 2: Sum the Range of Worksheets

To make the formula of addition easier, you can make use of SUM function to add the value in the same cell across a range of sheets. The formula is, for example:

=SUM('January:December'!T1)
All worksheets between January and December will be totaled, which is true also when you add a new sheet between them. However, any sheets before and after the range won’t be counted.

Method 3: Sum All Worksheets

If you want to ensure that all value of the same cell on all worksheets are total, just enter the following formula:

=SUM('*'!T1)

By |2016-12-10T21:41:07+00:00December 10th, 2016|Categories: Office|Tags: , |0 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.