Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to count table columns in Excel
  • How to calculate average last N values in a table in Excel
  • Create Scatter Chart in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How To Compare Two Lists in Excel

References

  • Left Lookup in Excel
  • Count rows with at least n matching values
  • Extract all partial matches in Excel
  • How to use Excel FORMULATEXT function
  • Offset in Excel

Data Validations

  • Data validation must not exist in list
  • Excel Data validation date in next 30 days
  • Excel Data validation must not contain
  • Excel Data validation require unique number
  • Excel Data validation exists in list

3D SUMIF for multiple worksheets in Excel

by

This tutorial shows how to 3D SUMIF for multiple worksheets in Excel using the example below;

Formula

=SUMPRODUCT(SUMIF(INDIRECT
("'"&sheets&"'!"&"range"),criteria,
INDIRECT("'"&sheets&"'!"&"sumrange")))

Explanation

To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like this:

=SUMPRODUCT(SUMIF(INDIRECT
("'"&sheets&"'!"&"D4:D5"),B9,
INDIRECT("'"&sheets&"'!"&"E4:E5")))

The data on each of the three sheets being processed looks like this:

Explanation

First of all, note that you can’t use SUMIFs with a “normal” 3D reference like this:

Sheet1:Sheet3!D4:D5
This is the standard “3D syntax” but if you try to use it with SUMIF, you’ll get a #VALUE error. So, to workaround this problem you can use a named range “sheets” that lists each sheet (worksheet tab) that you want to include. However, to build references that Excel will interpret correctly, we need to concatenate the sheet names to the ranges we need to work with and then use the INDIRECT to get Excel to recognize them correctly.

Also, because the named range “sheets” contains multiple values (i.e. its an array), the result of SUMIF in this case is also an array (sometimes called a “resultant array). So, we use SUMPRODUCT to handle it, since SUMPRODUCT has the ability to handle arrays natively without requiring Ctrl-Shift-Enter, like many other array formulas.

Alternatively;

The example above is somewhat complicated. Another way to handle this problem is to do a “local” conditional sum on each sheet, then use a regular 3D sum to add up each value on the summary tab.

To do this, add a SUMIF formula to each sheet sheet that uses a criteria cell on the summary sheet. Then when you change the criteria, all linked SUMIF formulas will update.

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Not Equal To ‘<>‘ operator in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Display Date is same month in Excel
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Convert Unix time stamp to Excel date
  • Get days between dates ignoring years in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • How to randomly assign data to groups in Excel
  • How to randomly assign people to groups in Excel
  • If cell contains one of many things in Excel
  • Map text to numbers in Excel

General

  • Index and match on multiple columns in Excel
  • Delete Blank Rows at Once in Excel
  • AutoFit Column Width, AutoFit Row Height in Excel
  • How to get amount with percentage in Excel
  • How to add sequential row numbers to a set of data in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning