Excel – Use Sumproduct() instead of Sumifs() or Countifs()

Sumifs() and Countifs() are formulas in Excel, that sum or count a range with multiple conditions.

Unfortunately, these formulas do not work, when you want to refer to a closed workbook. That’s where the magic of the Sumproduct() formula comes. It is able to do anything those 4 formulas (Sumif, Sumifs, Countif, Countifs) can do. And it works on closed workbooks.

Let’s see the following example: We have two ranges (Range 1 and Range 2). Range 1 is the sum range, Range 2 is condition range.

In the first example, we want to count the values in range 1, for which the corresponding ones in range 2 are vitosh, gosho or yulia. This is how the formula looks like:

And this is the result:

In order to add more conditions, you should multiply the first condition with the second one like this:

If you want to use Sumproduct() as an alternative of Sumifs() this is how it looks like with two conditions:

And with one condition, it is without the multiplication of the above formula:

That’s all folks! Some of the materials are available in my GitHub repository.

But simply copy & paste should be enough! Cheers!