Sum the Same Cell in Multiple Microsoft Excel Worksheets
Few people know how to do this, but once you learn, you'll never forget.
Suppose you have twelve worksheets named January through December. You want
to sum the total of cell A2 of all these worksheets on a sheet called Summary.
First, make sure your Summary worksheet is not amidst the others. In other
words, make sure it appears at the far right or far left of the monthly
worksheets. Whatever you do, you'll want all the worksheets you're summing to
be side-by-side, with no "foreign" worksheets in between.
Then, just write your formula as shown below.
Suppose your worksheets aren't in any kind of sequential
order. Or you have 50 worksheets, and you only want to sum 30 of them, but
you're constantly adding worksheets, so the beginning and end worksheet names
In this case, place a blank worksheet to the left of the first
worksheet you want to include in the formula. Call that worksheet "First".
Insert another blank worksheet after the last worksheet you want to include in
the formula. Call that worksheet "Last". You can even hide these worksheets!
Then, your formula will be:
What if you add another worksheet between the first and last
worksheet you use? Nothing! It'll add the cells in the new worksheet, too. If
you don't want the values of a certain worksheet added, keep the worksheet
outside of between the first and last worksheets you use in your formula.
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved