SUMPRODUCT v SUMIFS

SUMIFS is more efficient overall but SUMPRODUCT is far more versatile.

The big difference between the two is that SUMIFS can only be used for straight comparisons. You can't "manipulate" the criteria while with SUMPRODUCT you can "manipulate" the criteria.

For example, in SUMPRODUCT you can do something like this to manipulate a criteria:

MONTH(A1:A10)=5

You can't do that in SUMIF(S).

Well, SUMIFS didn't exist before Excel 2007 so SUMPRODUCT was routinely used in earlier versions for multi-conditional summing (and still is if you are using one of those versions).

Apart from that you can use SUMPRODUCT in some situations where SUMIFS won't work,

e.g. the most basic use of SUMPRODUCT, multiplying two ranges and summing the results

=SUMPRODUCT(A2:A100,B2:B100)

or counting rows where one range is greater than another

=SUMPRODUCT((A2:A100>B2:B100)+0)

or conditional summing when the criteria and sum ranges are different sizes

=SUMPRODUCT((A2:A100="x")*B2:Z100)

or this sort of use for calculating variable rates.

There are many other instances where SUMPRODUCT can be used but SUMIFS can't.......but, of course, if you can use SUMIFS it will generally be more efficient and is therefore preferable for basic multiconditional summing

You're welcome. Thanks for the feedback!

.