K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • UPCOMING EVENTS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • CONFERENCES
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • UPCOMING EVENTS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • CONFERENCES
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

Ranges on Multiple Worksheets

5/3/2020

2 Comments

 
When you need to refer to a range of cells, you enter the starting and ending point of the range, and you then separate those points by a colon. The following formula will give you the total of all cells in the range A1 through C4:.

=SUM(A1:C4)

What if you want to refer to a range of cells in multiple worksheets in your workbook. For example, how can you get a cell to return the sum of each cell A1 on the first three worksheets in your workbook? If the worksheets are named Alan1, Alan2 and Alan 3 then you would create the following formula:

=SUM(Alan1:Alan3!A1)

To calculate the sum of all cells in the range A1 through C5 on each worksheet, you would use the following formula:

=SUM(Alan1:Alan3!A1:C5)

This notation can be hard to remember. With the mouse, it is easy to build this formula with the following steps:

  1. Click on the cell where you want to enter your formula.
  2. Then enter an = sign and the first part of the function, followed by an opening parenthesis. For the examples given above, you would enter =SUM(.
  3. Click on the sheet tab of the first sheet in the range.
  4. Next hold down the Shift key as you click on the sheet tab of the last sheet in the range.
  5. Use the mouse to select all the cells in the range on the visible worksheet.
  6. Press Enter.

Alan Salmon

2 Comments
Barry
5/5/2020 01:28:02 pm

Shouldn't the second formula above be "=SUM(Alan1:Alan3!A1)"?

Reply
Ward link
5/5/2020 02:36:43 pm

Thanks Barry, it has been corrected.

Reply



Leave a Reply.

    Categories

    All
    Adobe
    Advisory
    AI
    Artificial Intelligence
    Automation
    Business Intelligence
    Cloud Computing
    Collaborate
    Collaboration
    COVID 19
    COVID-19
    CPA
    Cybersecurity
    Ecommerce
    Excel
    Forecast Sheet
    Google
    Grammarly
    Internal Priorities
    KPIs
    LET
    Management Reports
    Microsoft 365
    Microsoft Teams
    Office
    Office 365
    Online Shop
    Outsourcing
    PivotTables
    Power BI
    PowerPoint
    Productivity
    Quickbooks Online
    Ransomware
    Security
    Small Business Accounting Software
    Technology
    Tech Update
    Windows
    Windows 10
    Word
    Work At Home
    Zoho

    Authors


    Ward Blatch
    Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

    Tommy Stephens
    Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

    Randy Johnson
    Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com

    RSS Feed

Training & Education

Webinars
Upcoming Events
Road to Excellence Online Seminar
Seminars
Conferences
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact

K2E Canada Inc.  |  591 Highway 221  |  North Kingston, NS Canada B0P 1R0  |  (902) 200-9207
© 2020 K2E Canada Inc. ALL RIGHTS RESERVED.