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:

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

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

=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’

=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

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

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

In this formula, the previously written range reference:

'Step 3'!D3:D7

Is replaced by:

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

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

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:

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

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

=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)