This page will show the simple formulas to 3D sum the identical ranges for multiple worksheets using the SUMIF function in Excel. Let’s get started!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
General Formula:
- The below formula will help to sum the same range in one or more sheets using the SUMIF Function.
=SUMPRODUCT(SUMIF(INDIRECT(“‘”&sheets&”‘!”&”range”),criteria,INDIRECT(“‘”&sheets&”‘!”&”sum range”)))
Syntax Explanations:
- SUMPRODUCT – This function will help to multiply the corresponding arrays or ranges and returns the sum of the products. Read more on the*SUMPRODUCT Function*.
- SUMIF – It will return the sum of cells that meet a single condition. Read more on the SUMIF Function.
- INDIRECT – In Excel, the INDIRECT Function helps to return a valid reference from a given text string.
- SHEETS – The Sheets function returns the number of sheets in a given reference.
- Comma symbol (,) – It is a separator which helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
- Range – It represents the input range from your workbook.
- Criteria – It is the condition that is used to count the values.
Example:
- Now, we will see how to sum the same range in one or more sheets using the SUMIF function.
- Refer to the below image.
- It shows the input ranges of multiple worksheets.
- From the input range, we are going to find the sum of the project hours in the individual worksheets.
- Let’s see the below image, to sum the values.
- First, we will enter the sheet names in Column B and define the name as Sheets.
- Then, enter the given formula in the formula bar section.
- Finally, it will sum the same range from the above three sheets and display the result as shown in the below image.
Bottom-Line:
From this tutorial, we explained the formulas to 3D sum the identical ranges for multiple worksheets using the SUMIF function in Excel Office 365. If you have any doubts, feel free to share it with us. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *and Excel Formulas *!!
Top comments (0)