How to Use 3D SUMIF for Multiple Worksheets in Excel

Sometimes we need to sum identical ranges across the different worksheets. We can use a formula based on SUMIF, INDIRECT & SUMPRODUCT functions for this purpose. In this article, we are going to learn about how we can apply a 3D SUMIF formula for multiple worksheets. Moreover, we’ll also explore the application of the SUMPRODUCT and SUM, and VLOOKUP functions across multiple worksheets.

The following image shows an overview of the article which represents the removal of specific characters in Excel.

Overview of using 3D SUMIF for multiple worksheets with Excel functions

How to Use 3D SUMIF for Multiple Worksheets in Excel: 2 Ways

In the following, I have shared 2 simple and easy ways to use 3D SUMIF for multiple worksheets. Follow the instructions below.

Now, let’s assume the January Sales Volume in the Jan worksheet. Here, the B4:D9 cells contain the Employee, Project, and Sales Volume columns respectively.

Sample dataset for the month of January including Employee, Project, Sales Volume

In a similar manner, we have the dataset for the month of February.

Sample dataset for the month of February including employee, project, sales volume

Likewise, we have the dataset for the month of March.

Sample dataset for the month of March including employee, project, sales volume

Lastly, we want to summarize the sales data in the worksheet shown below.

Summary dataset to calculate the total sales from multiple worksheets using 3D SUMIF for multiple worksheets

1. Combining SUMIF, INDIRECT, and SUMPRODUCT Functions

In order to use 3D Referencing with the SUMIF you will need to combine it with the INDIRECT and SUMPRODUCT functions to sum values from multiple worksheets. Follow the instructions below.


  • First, choose a cell (C5) and write the below formula down-

Formula Breakdown:

  • SUMIF(INDIRECT(“‘”&Sheets&”‘!”&”C5:C9”),B5,INDIRECT(“‘”&Sheets&”‘!”&”D5:D9”)) →Here C5:C9 is the required criteria range for each sheet to sum by, B5 is the specific criteria, D5:D9 is the same range of each sheet to sum. The Sheets function will return the sheet number and the INDIRECT function will help to return a valid reference.
  • SUMPRODUCT(SUMIF(INDIRECT(“‘”&Sheets&”‘!”&”C5:C9”),B5,INDIRECT(“‘”&Sheets&”‘!”&”D5:D9”))) →This will multiply the values of the corresponding range and return the added value of each sheet.

Formula of SUMIF, INDIRECT, SUMPRODUCT to sum data from multiple worksheet

  • Second, press ENTER and drag the “Fill Handle” down to fill.
  • Finally, you will get the sum value according to the criteria from multiple worksheets. Simple isn’t it?

Final output 3D SUMIF for multiple worksheets after summing total sales volume for projects

Read More: How to Sum If Cell Contains Number and Text in Excel

2. Utilizing SUMIF Function for 3D Referencing from Multiple Worksheets

If you find the previous method critical then you can sum multiple data from multiple worksheets using the SUMIF function only. Go through the steps below to learn.


  • Similarly, choose a cell (C5) and apply the below formula down-


  • SUMIF(Jan!C5:C9,SUMIF!B5,Jan!D5:D9)→ In this part, the SUMIF function is summing value from range (Jan!C5:C9) with criteria from the cell (SUMIF!B5). Finally, summing the sum_range(Jan!D5:D9). Similarly, for Feb and Mar

Formula of SUMIF to sum sales volume for projects from multiple worksheets

  • Then press ENTER and drag down the “Fill Handle”.
  • In conclusion, you will get the final result in your hands.

Final result of 3D SUMIF for multiple worksheets summing sales volume for projects

How to Apply SUMPRODUCT Function Across Multiple Sheets

In some situations, you can also combine the SUMPRODUCT and SUM functions to sum values with multiple criteria from multiple sheets.


  • In the same fashion, select a cell (C5) and put the below formula-

Formula of SUMPRODUCT to sum sales volume across multiple sheets

  • Simply, hit the ENTER key and pull the “Fill Handle” down.
  • In summary, you will get the final result summing from multiple sheets.

Result of SUMPRODUCT function to find the total sales of projects across multiple sheets

How to VLOOKUP and SUM Across Multiple Sheets

Here in this part, we have also come up with a solution using the VLOOKUP and SUM functions. You can follow the instructions below to learn.

Suppose we have a dataset of the Sales Volume of multiple products for the month of June in a different worksheet.

Sample dataset of June sales volume containing product and sales volume

Similarly, we have the dataset for the month of July.

Sample dataset of July sales volume containing product and sales volume


  • First, start with selecting a cell (C5) and writing the below formula down-


  • The VLOOKUP function will look up for values from a provided column ($B$5:$C$9) across multiple sheets.
  • Then, the SUM function will sum up the value extracted using the VLOOKUP function.

Formula of VLOOKUP and SUM for summing sales volume from multiple sheets

  • To finish, click ENTER and drag the “Fill Handle” down.
  • Within a blink of an eye, you will get the total sales volume in your hands.

Final output summing sales volume across multiple worksheets with VLOOKUP and SUM functions

Admittedly, we’ve skipped some examples of VLOOKUP and SUM functions to look up values across multiple worksheets, which you may explore.

Download Practice Workbook


In this article, I have tried to cover almost all the methods on how to use 3D SUMIF for multiple worksheets in Excel. Now, take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Also, inform us in the comment section about your experience. Stay tuned and keep learning.

Related Articles

<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF