Excel Formula to Sum Cell Values from Multiple Worksheets

Table of Contents

Excel Formula to Sum Cell Values from Multiple Worksheets

What is it:

If you have multiple work sheets in your excel workbook and you want to create a Summary page with totals from all the individual work sheets then you can use the below excel formula or excel shortcut to do the same.

For Eg: Your Excel workbook has 5 worksheets like Shope1, Shope2, Shope3……Shope5 and each sheet has Total Sales value in cell A11, now you want to create a Summary sheet with sum total of all shopes cell in A11. The one way of doing the same is go to Summary Sheet and use the below excel formula

=Shope1!A11+Shope2!A11+=Shope3!A11+Shope4!A11+Shope4!A11+Shope5!A11

Why:

The above excel formula is not all practical if you have 100s of sheets in your workbook and with entirely different names. That’s when the below formula is helpful.

How to

Excel Formula to Sum Cell Values from Multiple Worksheets





Case 1. If all the sheets are in sequential order then go to the Summary Sheet or the sheet were you want the Sum total of all the sheets and enter the below excel formula:Excel Formula To Sum Cell Values From Multiple Worksheets

       

=sum(Shope1:Shope5!A11)

Case 2. If suppose sheets are not in sequential order or sheet names are entirely different then insert a sheet named First before all the individual sheets and another Sheet named Last at the end of all the individual sheets. Then go to Summary Sheet and enter the below excel formula:

Excel Formula To Sum Cell Values From Multiple Worksheets

 

=sum(First:Last!A11)

Anson Antony

Anson Antony

Anson is a contributing author and founder at ASKEYGEEK.com. Learning anything new has always been his passion, ASKEYGEEK.com is an outcome of his passion for technology and business. He has got a decade of versatile experience in Business Process Outsourcing, Finance & Accounting, Information Technology, Operational Excellence & Business Intelligence. During the tenure, he had worked for organizations like Genpact, Hewlett Packard, M*Modal and Capgemini in various roles and responsibilities. Outside business and technology, he is a movie buff who spends hours together watching and learning Cinema and a Film Maker too!

One Response

Leave a Reply

Your email address will not be published.

Launching soon...

E-book price at Amazon will be $14.99 

A Practical Guide To Online Business
Congratulations!
You Made It,
Don't Close!

Enter your chance to win our Premium Membership License for FREE! ( Normal Price $199/Year)

Thank you for your visit!

This popup won’t show up to you again!!!

Premium Membership Giveaway - Popup
small_c_popup.png

Let's have a chat

Fill out the form below to consult with us

*Please check your Inbox or Junk Folder for the Confirmation Email. You must confirm your Company email address before we could revert.

Share to...