# How to Use 3D SUMIF for Multiple Worksheets in Excel

Get FREE Advanced Excel Exercises with Solutions!

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. ## 3D SUMIF for Multiple Worksheets in Excel: 2 Ways to Use

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. In a similar manner, we have the dataset for the month of February. Likewise, we have the dataset for the month of March. Lastly, we want to summarize the sales data in the worksheet shown below. ### 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.

Steps:

• First, choose a cell (C5) and write the below formula down-
`=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"C5:C9"),B5,INDIRECT("'"&Sheets&"'!"&"D5:D9")))`

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. • 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? 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.

Steps:

• Similarly, choose a cell (C5) and apply the below formula down-
`=SUMIF(Jan!C5:C9,SUMIF!B5,Jan!D5:D9)+SUMIF(Feb!C5:C9,SUMIF!B5,Feb!D5:D9)+SUMIF(Mar!C5:C9,SUMIF!B5,Mar!D5:D9)`

Where,

• 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 • Then press ENTER and drag down the “Fill Handle”.
• In conclusion, you will get the final result in your hands. Read More: How to Sum If Cell Contains Number in Excel

## How to Apply SUMPRODUCT Function Across Multiple Sheets

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

Steps:

• In the same fashion, select a cell (C5) and put the below formula-
`=SUM(SUMPRODUCT((Jan!C5:C9=SUMPRODUCT!B5)*(Jan!D5:D9)),SUMPRODUCT((Feb!C5:C9=SUMPRODUCT!B5)*(Feb!D5:D9)),SUMPRODUCT((Mar!C5:C9=SUMPRODUCT!B5)*(Mar!D5:D9)))` • Simply, hit the ENTER key and pull the “Fill Handle” down.
• In summary, you will get the final result summing from multiple sheets. Read More: Excel SUMIF Not Working

## 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. Similarly, we have the dataset for the month of July. Steps:

• First, start with selecting a cell (C5) and writing the below formula down-
`=SUM(VLOOKUP(B5,Jun!\$B\$5:\$C\$9,{2},FALSE),VLOOKUP(B5,Jul!\$B\$5:\$C\$9,{2},FALSE))`

Where,

• 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 • 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. Admittedly, we’ve skipped some examples of how to apply the VLOOKUP and SUM functions to look up values from multiple worksheets, which you may explore.

## Conclusion

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 Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  