How to sum multiple worksheets in Excel

If you want to calculate the sum or total when working with cell data located in multiple worksheets, this article write for you!

To sum the same range in one or more sheets, you can use the SUM formula with a special syntax called a “3d reference”.

A reference that refers to the same cell or range on multiple sheets is called a 3-D reference. A 3-D reference is a useful and convenient way to reference several worksheets that follow the same pattern and cells on each worksheet contain the same type of data, such as when you consolidate budget data from different departments in your organization.

Formula:

=SUM(Sheet1:Sheet3!coordinates) or =SUM(Sheet1!coordinates,Sheet2!coordinates,Sheet3!coordinates,Sheet4!coordinates,Sheet5!coordinates)

Example:

Sheet1:

A B C
1 Year Wins Losses
2 2013 9 3
3 2012 8 4
4 2011 10 2
5 2010 11 1
6 2009 9 3
7 2008 7 5

Sheet2:

A B C
1 Year Wins Losses
2 2017 9 3
3 2006 11 1
4 2005 9 3
5 2004 10 2
6 2003 7 5
7 2002 7 6

Try formula: =SUM(Sheet1:Sheet2!B3)

That will sum B3 of Sheet1 and B3 of Sheet2

8 + 11 = 19