Sum if Across Multiple Sheets – Excel & Google Sheets

This tutorial will demonstrate how to use the SUMPRODUCT and SUMIFS Functions to sum data that meets certain criteria across multiple sheets in Excel and Google Sheets.

sum if multiple sheets Main Function

Regular Sum Across Multiple Sheets

Sometimes your data might span several worksheets in an Excel file. This is common for data that is collected periodically. Each sheet in a workbook might contain data for a set time period. We want a formula that sums data contained in two or more sheets.

The SUM Function allows you to easily sum data across multiple sheets using a 3D Reference:

sum if multiple sheets 01

However, this is not possible with the SUMIFS Function. Instead we must use a more complicated formula.

Sum if Across Multiple Sheets

This example will sum the Number of Planned Deliveries for each Customer over multiple worksheets, each holding data relating to a different month, by using the SUMIFS, SUMPRODUCT, and INDIRECT Functions:

sum if multiple sheets 02

Let’s walk through this formula.

Step 1: Create a SUMIFS Formula for 1 Input Sheet Only:

We use the SUMIFS Function to sum the Number of Planned Deliveries by Customer for a single input data sheet:

sum if multiple sheets Step01

Step 2: Add a Sheet Reference to the Formula

We keep the formula result the same, but we specify that the input data is in the sheet called ‘Step 2’

sum if multiple sheets Step02

Step 3 : Nest Inside a SUMPRODUCT Function

To prepare the formula to perform SUMIFS calculations over multiple sheets and then to sum the results together, we add a SUMPRODUCT Function around the formula

sum if multiple sheets Step03

Using the SUMIFS Function on one sheet yields a single value. Across multiple sheets, the SUMIFS function outputs an array of values (one for each worksheet). We use the SUMPRODUCT Function to total the values in this array.

Step 4: Replace the Sheet Reference with a List of Sheet Names

We wish to replace the Sheet Name part of the formula with a data list containing the values: Jan, Feb, Mar, and Apr. This list is stored in the cells F3:F6.

The INDIRECT Function to ensures that the text list showing Sheet Names is treated as part of a valid cell reference in the SUMIFS Function.

sum if multiple sheets 02In this formula, the previously written range reference:

Is replaced by:

The quotation marks makes the formula difficult to read, so here it is shown with added spaces:

Using this way of referencing a list of cells also allows us to summarize data from multiple sheets which do not follow a numerical list style. A standard 3D reference would require the sheet names to be in the style: Input1, Input2, Input3, etc., but the example above allows you to use a list of any Sheet Names and to have them referenced in a separate cell.

Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

But these formulas will not work properly when copy and pasted elsewhere in your file. Instead, you should use locked cell references like this:

Read our article on Locking Cell References to learn more.

Sum If Across Multiple Sheets in Google Sheets

Using the INDIRECT Function to reference a list of sheets in a SUMPRODUCT and SUMIFS Function is not currently possible in Google Sheets.

Instead, separate SUMIFS calculations can be made for each input sheet and the results added together:

sum if multiple sheets Google Function







Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!