Skip to main content

3D sum or sumif across multiple worksheets in Excel

Author: Siluvia Last Modified: 2021-10-21

This tutorial talks about how to sum the same range of cells across multiple consecutive or discontiguous worksheets in Excel.

How to perform a 3D sum across multiple consecutive worksheets?
How to perform a 3D sumif across multiple discontiguous worksheet?

Tip: Before applying the formulas below, make sure that the worksheets that you want to sum should have the same data structure and position as shown in the screenshot below.


How to perform a 3D sum across multiple consecutive worksheets?

For the monthly sales workbook shown below, to get a year-end sales summary for each salesperson across all worksheets, you can easily get it done with the SUM function.

Generic Formulas

=SUM(first_sheet:last_sheet!cell)

Arguments

First_sheet: The name of the first worksheet where you want to summarize the data;
Last_sheet: The name of the last worksheet where you want to summarize the data;
Cell: The cell reference which you want to sum the values from.

How to use this formula?

1. Select a cell in the summary worksheet (here I select C5).

2. Copy or enter the below formula into it and press the Enter key. Select the result cell and then drag its AutoFill Handle down to get the sales summary for other salespersons.

=SUM(Jan:Dec!C2)

Explanation of this formula

=SUM(Jan:Dec!C2): The SUM function sums cell C2 from the worksheet Jan to Dec in the current workbook.


How to perform a 3D sumif across multiple discontiguous worksheet?

Take the above workbook as an example, to get a year-end sales summary for each salesperson across discontiguous worksheets such as worksheets Jan, Mar, Apr and Oct, you can combine the SUMPRODUCT, SUMIF and INDIRECT functions to get it down.

Tip: Before applying the formula, you need to list all needed sheet names in the summary worksheet and give a name to the range. In this case, Sheet_names is the name of the range that lists the sheet names.

Generic formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&name_of_sheet_names_range&"'!" & "criteria_range"),criterion,INDIRECT("'"&name_of_sheet_names_range&"'!" &"sum_range")))

Arguments

Name_of_sheet_names_range: It is the range name of the sheet names list;
Criteria_range: The same criteria range in each worksheet that you will sum cells based on;
Criterion: A cell references the criterion in the main worksheet that matches the first value in the criteria_range you will sum cells based on;
Sum_range: The same range of cells in each sheet from which you want to sum the values.

How to use this formula?

1. Select a blank cell in the summary sheet.

2. Copy or enter the below formula into it and press the Enter key. Select the result cell and then drag its AutoFill Handle down to get the sales summary for other salespersons in specified worksheets.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet_names&"'!"&"A2:A7"),A9,INDIRECT("'"&Sheet_names&"'!"&"C2:C7")))


Related functions

Excel SUM function
The Excel SUM function adds values.

Excel SUMPRODUCT function
The Excel SUMPRODUCT function can be used to multiply two or more columns or arrays together, and then get the sum of products.

Excel SUMIF function
The Excel SUMIF function can help to sum cells based on one criterion

Excel INDIRECT function
The Excel INDIRECT function converts a text string to a valid reference.


Related formulas

Calculate running total
This tutorial explains a simple formula to help calculating running total in Excel.

Get subtotal by invoice number
This tutorial explains a formula based on the COUNTIF and SUMIF functions to get subtotal by invoice number in Excel.

Subtotal by colors
This step-by-step guide helps you perform subtotal by color with the SUMIF function.

Two-way summary counting
In this tutorial, we will discuss how to use the COUNTIFS function to create two-way summary count in Excel.


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations