In this Article

*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**:

1 |
=SUM(Sheet1:Sheet2!A1) |

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:

1 |
=SUMPRODUCT(SUMIFS(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3)) |

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:

1 |
=SUMIFS(D3:D7,C3:C7,H3) |

### 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’**

1 |
=SUMIFS('Step 2'!D3:D7,'Step 2'!C3:C7,H3) |

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

1 |
=SUMPRODUCT(SUMIFS('Step 3'!D3:D7,'Step 3'!C3:C7,H3)) |

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.

1 |
=SUMPRODUCT(SUMIFS(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3)) |

In this formula, the previously written range reference:

1 |
'Step 3'!D3:D7 |

Is replaced by:

1 |
INDIRECT("'"&F3:F6&"'!"&"D3:D7") |

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

1 |
INDIRECT ( " ' " & F3:F6 & " ' ! " & "D3:D7" ) |

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:

1 |
=SUMPRODUCT(SUMIFS(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3)) |

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

1 |
=SUMPRODUCT(SUMIFS(INDIRECT("'"&$F$3:$F$6&"'!"&"D3:D7"),INDIRECT("'"&$F$3:$F$6&"'!"&"C3:C7"),H3)) |

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:

1 2 3 4 |
=SUMIFS(Jan!D3:D7,Jan!C3:C7,H3) +SUMIFS(Feb!D3:D7,Feb!C3:C7,H3) +SUMIFS(Mar!D3:D7,Mar!C3:C7,H3) +SUMIFS(Apr!D3:D7,Apr!C3:C7,H3) |