Excel: How to Use SUMIF Across Multiple Sheets


You can use the following basic syntax to use SUMIF across multiple multiple sheets in Excel:

=SUMIF(First!B2:B11, "Guard", First!C2:C11)+SUMIF(Second!B2:B8, "Guard", Second!C2:C8)

This particular example will sum all of the following values:

  • The values in the range C2:C11 of the sheet titled First only where the corresponding cells in the range B2:B11 contain “Guard”.
  • The values in the range C2:C8 of the sheet titled Second only where the corresponding cells in the range B2:B18 contain “Guard”.

The following example shows how to use this formula in practice.

Example: How to Use SUMIF Across Multiple Sheets in Excel

Suppose we have a sheet titled First that contains information about the best pro basketball players during the first half of the regular season:

And suppose we have another sheet titled Second that contains information about the best pro basketball players during the second half of the regular season:

Suppose we would like to calculate the sum of points for players in both sheet who have a value of “Guard” in the Position column.

We can type the following formula into the sheet called Summary to calculate this sum:

=SUMIF(First!B2:B11, "Guard", First!C2:C11)+SUMIF(Second!B2:B8, "Guard", Second!C2:C8)

The following screenshot shows how to use this formula in practice:

The formula returns a value of 176.

This tells us that the sum of values in the Points column for all players who have a value of “Guard” in the Position column in the First and Second sheets is 176.

Note: In this example we added the results of two SUMIF functions but you can add the results of as many SUMIF functions as you’d like depending on how many sheets you’re using.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: How to Use SUMPRODUCT Across Multiple Sheets
Excel: How to use SUM Across Multiple Sheets
Excel: How to Create Pivot Table from Multiple Sheets

Featured Posts

Leave a Reply

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