Alternative for SUMIF and SUMIFS - Part 2 (SUMPRODUCT)

We already know the primary challenge of using SUMIF or SUMIFS i.e. unable to sum more than a single column or row as a sum range. Many of us in the course of our excel usage may have experienced that SUMIFS has a bigger disadvantage when used across workbooks i.e. the source workbook should be open inorder to enable to computation in the destination workbook. If the source workbook remains closed, the result in the destination workbook will be "#VALUE".

In my previous post, the alternative suggested not only does overcome the "#VALUE" challenge but also allow users to sum a larger range. In this post, I was able to decipher yet another alternative. I'm sure expert users may already be using this however to help other users, this post can be a ray of hope.

We may have used the SUMPRODUCT function to calculate weighted average. Extrapolating the usage of SUMPRODUCT with condition or conditions can help you achieve the SUMIFS goal. 

Syntax: 

=SUMPRODUCT( (Condition1) * (Condition2) * (ConditionN) * (sumrange))

The table below is an example where in we can compute the total of Range1 and Range2 based on a combination of Criteria1 and Criteria2.

Source Table (may or may not be in the same Workbook)

Suppose, we need to find the sum of the Range1 and Range2 if Criteria1 is "A" and Criteria2 is "w" let us see the syntax of the SUMPRODUCT function to be used.

If is it is in the same file in Sheet1:

=SUMPRODUCT((Sheet1!$B$2:$B$9=B2)*(Sheet1!$C$2:$C$9=C2)*Sheet1!$D$2:$E$9)

if is is in a different file (named "Sample.xlsx") in Sheet1

=SUMPRODUCT(([Sample.xlsx]Sheet1!$B$2:$B$9=I18)*([Sample.xlsx]Sheet1!$C$2:$C$9=J18)*[Sample.xlsx]Sheet1!$D$2:$E$9)

This will give you the identify the values against the combination of "A" and "w" and give the result as 14.

In this case you don't have to use the array feature unlike my previous post.

Hope this helps you in using your excel workbooks more efficiently and flawlessly.