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:

1 2 3 |
=SUMPRODUCT((B2:B6=C2)*1) +SUMPRODUCT((B8:B13=C8)*1) +SUMPRODUCT((B16:B20=C16)*1) |

And this is the result:

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

1 2 3 |
=SUMPRODUCT(((B2:B6=C2)*1)*(A2:A6=D2)) +SUMPRODUCT(((B8:B13=C8)*1)*(A8:A13=D8)) +SUMPRODUCT(((B16:B20=C16)*1)*(A16:A20=D16)) |

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

1 2 3 |
=SUMPRODUCT(((B2:B6=C2)*1)*(A2:A6=D2)) *SUMPRODUCT(((B8:B13=C8)*1)*(A8:A13=D8)) *SUMPRODUCT(((B16:B20=C16)*1)*(A16:A20=D16)) |

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!