Excel 3D SUMIF for Multiple Worksheets

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.


Practice Workbook

Download the following workbook and exercise.


Introduction to the Excel SUMIF Function

The SUMIF function sums up the values based on a particular condition.

  • Syntax:

=SUMIF(range, criteria, [sum_range])

  • Arguments:

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,

Dataset Introduction with Multiple WorksheetsSheet2 values,

Dataset Introduction with Multiple Worksheets

Sheet3 values,

Dataset Introduction with Multiple Worksheets

Finally, the worksheet where we want to see the final result,

Dataset Introduction with Multiple Worksheets

 

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.

STEPS:

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

Steps to Use 3D SUMIF for Multiple Worksheets

  • Now select Cell C5.
  • After that, type the formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"C5:C9"),B5,INDIRECT("'"&Sheets&"'!"&"D5:D9")))

Steps to Use 3D SUMIF for Multiple Worksheets

  • In the end, hit Enter and use Fill Handle to autofill the cells.

Steps to Use 3D SUMIF for Multiple Worksheets

How Does the Combined 3D SUMIF Formula Work?

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.


Conclusion

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.


Related Readings

How to Combine Excel SUMIF & VLOOKUP Across Multiple Sheets

Excel INDEX-MATCH If Cell Contains Text

How to Pull Data From Another Sheet Based on Criteria in Excel

Excel Dynamic VLOOKUP (with 3 Formulas)

Remove First Character from String in Excel (6 Quick Ways)

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. Here I will 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

Leave a reply

ExcelDemy
Logo