How to Use SUMIFS Function in Excel with Multiple Sheets

In Excel, it’s common to organize your data across multiple sheets. For example, you might track sales for different regions like North, South, and East on separate tabs. But when you want to calculate totals based on specific conditions, such as the total sales of T-Shirts, it becomes a bit more complex. That’s because the SUMIFS function doesn’t work directly across multiple sheets.

To solve this, we can use a smart combination of functions. By combining SUMIFS with INDIRECT and SUMPRODUCT function, you can easily pull data from different sheets and apply conditions like product name or category.

In this article, we’ll learn how to use the SUMIFS function in Excel to automatically calculate totals for each product across multiple sheets. It’s a practical way to simplify your reports and keep everything updated in one place.

In the following dataset, we’re working with four sheets labeled North, South, East, and Summary. Each regional sheet contains a list of products and their sales figures. The Summary sheet is where we’ll calculate the total sales of each product by combining data from all three regions.

Sheet: North
The North sheet includes a list of products such as T-Shirt, Jeans, and Shoes, along with their individual sales values for the North region.

Sheet: South
The South sheet has the same product list and structure, but with different sales numbers recorded for the South region.

Sheet: East
The East sheet also follows the same layout and contains the same products, each with its own set of sales values for the East region.

Sheet: Summary
In the Summary sheet, we’ve listed all product names in the first column. The second column is used to calculate the Total Sales for each product by pulling data from the North, South, and East sheets using a single formula.

The standard SUMIFS function works well when your data is on a single sheet. But if your data is split across several tabs, like North, South, and East, you can’t use SUMIFS directly. To make it work, we need to combine it with two additional functions such as SUMPRODUCT and INDIRECT.

This method allows you to apply the SUMIFS logic to multiple sheets at once, based on specific criteria like a product name. In this example, we’ll calculate the total sales for each product by pulling data from all three regional sheets into the Summary sheet.

Here’s how to apply this method step by step:

➤ Open the Summary sheet in your Excel, where you’ve already listed all product names in Column A. In Column B, we’ll use a formula to calculate the total sales for each item.
➤ Click on cell B2, next to the product T-Shirt.

➤ Now type the following formula

➤ Press Enter. This formula adds up all sales for T-Shirt by checking across the North, South, and East sheets.

For example, the total sales of T-Shirts in different regions:

North sheet: 120 + 80 + 350 + 450 = 1000
South sheet: 90 + 110 + 480 = 680
East sheet: 100 + 750 + 150 = 1000
Total: 1000 + 680 + 1000 = 2680

➤ Next, to copy the formula for the other products, use the fill handle to drag the formula down for the rest of the products like Jeans and Shoes. Excel will automatically update the criteria for the rest of the rows.

Another way to use the SUMIFS function across multiple sheets is by combining individual SUMIFS formulas with the AND (+) operator. This method is helpful when you want to apply the same criteria to multiple sheets and add the results together.

In this method, we write separate SUMIFS functions for each sheet and connect them with the + symbol. This works well when you’re dealing with a small number of sheets and prefer a more direct result.

Here’s how to use this method:

➤ Open your Excel file and go to the Summary sheet.
➤ Click on cell B2 and enter this formula:

➤ Now, press Enter. This formula calculates the total sales for T-Shirt by applying the same condition to the Product column in each sheet and adding the results together.

➤ Next, to copy the formula for the remaining products, drag the fill handle down to the next rows. Excel will update the criteria to A3, A4, and so on, returning the total sales for each item across all regions.

You can also use the SUM function with SUMIFS to total values across multiple sheets. This method places them inside a single SUM function. It works the same way but keeps the formula shorter and more organized.

In this method, we use separate SUMIFS functions for each sheet but place them inside a single SUM function. It’s useful when you’re working with a few sheets and want to keep everything inside one function.

Here’s how to use this method:

➤ Open your Excel file and go to the Summary sheet.
➤ Click on cell B2 and enter this formula:

➤ Now, press Enter. This formula calculates the total sales for T-Shirt by applying the same condition to each sheet and adding all the results together using the SUM function.

➤ Next, drag the fill handle down to the next rows to copy the formula for the remaining products such as Jeans and Shoes.

Frequently Asked Questions

How to SUMIFS Across Multiple Sheets in Excel?

To use SUMIFS across multiple sheets, you need to combine it with INDIRECT and SUMPRODUCT functions. Excel doesn’t let the SUMIFS function work directly across sheets, so INDIRECT is used to reference each sheet’s range, and SUMPRODUCT adds up the totals.

Here’s how can you do that:

➤ Suppose you have three sheets named North, South, and East.
➤ In your Summary sheet, type the product name T-Shirt in cell A2.
➤ In cell B2, enter this formula:
=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&{“North”,”South”,”East”}&”‘!C2:C10″), INDIRECT(“‘”&{“North”,”South”,”East”}&”‘!A2:A10″), A2))
➤ Press Enter. This will calculate the total sales for the product in A2 by summing data from all three sheets.

Why doesn’t SUMIFS work directly across sheets?

The SUMIFS function is not designed to work across multiple sheets at once. It can only evaluate conditions within a single worksheet. That’s why we use INDIRECT to build references to each sheet individually and then wrap everything in SUMPRODUCT to combine the results.

Wrapping Up

Working with data across multiple sheets in Excel can be a complex task. But with the right combination of functions, it becomes much easier to manage. Using SUMIFS alongside INDIRECT and SUMPRODUCT allows you to apply conditions and gather totals from different tabs without copying or merging data manually.

This method is ideal for creating dynamic summary reports where the totals update automatically as values change in the source sheets. With just one formula, you can pull everything into a clear overview and save time on manual work. Try it out and see how much smoother your calculations become.