Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how we can use SUMIFS/INDEX/MATCH and SUMPRODUCT differently to achieve the same result. Let’s get started
In the captions below, we have HR Salary Data in Dynamic SUMPRODUCT and Dynamic SUMIFS sheet
Note, we have list data validation in cells M2 (List of unique department), and N2 (Gross Salary, Deduction, Net Salary) of both sheet.
SUMIFS/INDEX/MATCH
Using combination of SUMIFS/INDEX/MATCH functions, we want to dynamically calculate the total Gross Salary, Deduction & Net Salary for each selected department in cell O4
- SUMIFS adds the cells specified by a given set of conditions or criteria
- SUMIFS Syntax =SUMIFS(sum_range, criteria_range1, criteria1,…)
- INDEX returns a value of a reference of the cell at the intersection of a row and column in a given range
- INDEX Syntax =INDEX(array, row_num, [column_num])
- MATCH returns the relative position of an item in an array that matches a specified value in a specified order.
- MATCH Syntax =MATCH(lookup_value, lookup_array, [match_type])
- Solution
- In cell O4, execute the formula: =SUMIFS(INDEX(I4:K38,,MATCH(N2,I3:K3,0)),H4:H38,M2)
In the above formula, we wrapped INDEX and MATCH function inside the SUMIFS. In addition, because we deployed SUMIFS function, it comes important to skip the input value for the INDEX row_num argument.
Furthermore, I4:K38 is represent the array argument input values. MATCH(N2,I3:KK3,0) handles the INDEX function 4th argument which is [column_num]. H4:H38 is the SUMIFS criteria_range1 while M2 represent SUMIFS 3rd argument which is crtieria1
From the caption below, the Total Gross Salary for all IT employees is $278,500.00
When we filtered the department to show IT, we can see that the Gross Salary in the status bar matched the Gross Salary in cell O4
SUMPRODUCT
We want to replicate the above result using the SUMPRODUCT function
- SUMPRODUCT returns the sum of the product of corresponding ranges or arrays
- SUMPRODUCT Syntax =SUMPRODUCT(array1, [array2], [array3],…)
Using combination of SUMPRODUCT function, we want to dynamically calculate the total Gross Salary, Deduction & Net Salary for each selected department in cell O4
- In cell O4, execute the formula: =SUMPRODUCT(((H4:H38=M2)*(I3:K3=N2)),I4:K38)
The interpretation of the above formula is as follows:
If the values in H4:H38 equals to selected value in M2 and if the values in I3:K3 equals to the selected value in N2, then return the corresponding sum of the product in I4:K38 array
In the caption below, the Gross Salary of all the employees in IT department is $278,500.00 (same with the SUMIFS/INDEX/MATCH)
In the caption below, we filtered the department and select the Gross Salary filtered range. In the status bar, the result is the same with the value in cell O4
CONCLUSION
We have seen how to use SUMIFS/INDEX/MATCH and SUMPRODUCT functions to create dynamic calculations. The SUMPRODUCT method to me, is slightly straight-forward and easy to understand compared to SUMIFS/INDEX/MATCH method.
When using the SUMPRODUCT method, it is important to pay attention to brackets. In the SUMIFS/INDEX/MATCH method, it is important to always skip the INDEX 2nd argument which is row_num