# 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. ## 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: 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: 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: ### 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’ ### 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 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. In 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:

## 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. 