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 generate a 3D SUMIF formula for multiple worksheets.
Download the following workbook and exercise.
Introduction to the Excel SUMIF Function
The SUMIF function sums up the values based on a particular condition.
=SUMIF(range, criteria, [sum_range])
range: The range of values to sum
criteria: Condition to use in the selected range
[sum_range]: Where we want to see the result.
Excel 3D SUMIF for Multiple Worksheets
Dataset Introduction with Multiple Worksheets
Assuming we have multiple worksheets (Sheet1, Sheet2, Sheet3) with the values of employee names, their working projects (Project1, Project2), and the working hours. Now we are going to find the total working hours of each project from the multiple sheets.
Here Sheet1 value,
Finally, the worksheet where we want to see the final result,
Steps to Use 3D SUMIF for Multiple Worksheets
To execute the procedure, we have to combine a formula with some functions. Apart from using the SUMIF function, we’re going to use SUMPRODUCT and INDIRECT functions as well. The SUMPRODUCT function multiplies the values of an array or range and returns the added value of the products. And the INDIRECT function generally returns a valid reference from the given text string. That means it converts the text string into a dynamic valid reference.
- First, select the worksheet names that we want to use to sum in the required sheet.
- Next, go to the Name box and write down the name as Sheets.
- Now select Cell C5.
- After that, type the formula:
- In the end, hit Enter and use Fill Handle to autofill the cells.
How Does the Combined 3D SUMIF Formula Work?
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.
This will multiply the values of the corresponding range and return the added value of each sheet.
By using this method, we can easily sum identical ranges across the different worksheets. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.