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.
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.
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.
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.
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.
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 function.
- 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 VLOOKUP and SUM functions to look up values across multiple worksheets, which you may explore.
Download Practice Workbook
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
- How to Sum If Cell Contains Number in Excel
- How to Use Excel SUMIF with Greater Than Criterion
- How to Use Excel SUMIF to Sum Values Greater Than 0
- How to Use SUMIF to SUM Less Than 0 in Excel
- Sum If Greater Than and Less Than Cell Value in Excel
- How to Use Excel SUMIF Function Based on Cell Color
<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
