Difference Between SUMIFS and SUMPRODUCT in Google Sheets

0
210
SUMIFS or SUMPRODUCT

There are major difference between SUMIFS and SUMPRODUCT functions in Google Sheets. They may seem to you as similar in use. But it is not the case. SUMIFS or SUMPRODUCT, which one is better? To get the answer to this question read the pros and cons of SUMIFS and SUMPRODUCT below.

Difference Between SUMIFS and SUMPRODUCT

Below are the major differences between SUMIFS and SUMPRODUCT in Google Sheets, which I found. There is no official note available in this regard.

SUMPRODUCT can calculate the sum of the products from multiple equal sized arrays or ranges.

SUMIFS can only sum values from a single range.

Example:

Difference Between SUMIFS and SUMPRODUCT

SUMIFS Formula: For SUMIFS calculation we should first calculate the product under “Amount” field. This field then used in SUMIFS as this function can only sum ranges from a single range.

=SUMIFS(E2:E9,A2:A9,”=*”,B2:B9,”=*”)

SUMPRODUCT Formula: For SUMPRODUCT there is no need to first find the product in “Amount Field”. SUMPRODUCT nicely do it within the formula and also sum it.

=sumproduct(C2:C9*D2:D9)

SUMIFS uses logical approach while SUMPRODUCT mathematical operation.  As a result in any sum range if there is text value, SUMIFS as well as DSUM skip it. But SUMPRODUCT not. It returns error.

Example:

SUMPRODUCT error value not in SUMIFS

In SUMIFS and SUMPRODUCT, the use of DATE criterion wary slightly.

Date criteria in SUMIFS:

“>=”&date(2017,7,1)

Date criteria format in SUMPRODUCT:

>=DATE(2017,7,1)

Related: Date Difference as Criteria in SUMPRODUCT in Google Sheets

More Similar:  How to Include Same Field Twice in SUMIFS

SUMIFS is said to be faster if you use it for its core purpose. It can quickly check multiple ranges for conditions provided and return the result. For single range, you should better to use SUMIF.

The above are the major difference between SUMIFS and SUMPRODUCT.

Some Similarities Between SUMPRODUCT and SUMIFS

Infinite column reference is possible with SUMIFS and SUMPRODUCT. I could not, so far, found any problem using it.

Infinite range field with SUMIFS:

=SUMIFS(E2:E,A2:A,”=*”,B2:B,”=*”)

Infinite range or array field with SUMPRODUCT:

=sumproduct(C2:C*D2:D)

Both SUMPRODUCT and SUMIFS Can Handle Multiple Criterion.

A complex formula using SUMPRODUCT.

=sumproduct((A7:A14=“Philip Nida”)*((B7:B14=“North”)+(B7:B14=“South”))*(C7:C14>=DATE(2017,7,1))*(C7:C14<=DATE(2017,7,31))*(D7:D14))

SUMIFS alternative to the above formula.

=sumifs(D7:D14,A7:A14,“Philip Nida”,B7:B14,“North”,C7:C14,“>=”&date(2017,7,1),C7:C14,“<=”&date(2017,7,31))+
SUMIFS(D7:D14,A7:A14,“Philip Nida”,B7:B14,“South”,C7:C14,“>=”&date(2017,7,1),C7:C14,“<=”&date(2017,7,31))

Where to Use SUMPRODUCT Function?

The core purpose of SUMPRODUCT is to multiply corresponding components in an array and SUM it. SUMPRODUCT function should be used by keeping this in mind. It accepts criteria and text in array to become more flexible. That’s why we are able to use SUMPRODUCT function similar to SUMIFS.

Where to Use SUMIFS Function?

When you want to SUM a range with multiple conditions in different ranges use SUMIFS only.

Why We Tend to Use SUMPRODUCT Over SUMIFS?

We often tend to use SUMPRODUCT instead of SUMIFS. This is simply because in SUMPRODUCT also you can apply conditions. It’s because SUMPRODUCT deviates from its core purpose that is multiply corresponding components in an array. It accepts text fields in the array. When it accepts text fields, there should definitely be the feature to apply conditions. That feature we can extend to date and numeric fields also. So it behaves like SUMIFS.

SUMIFS is better for its core purpose only. It does its job nicely. You should use these two functions sensibly.

LEAVE A REPLY

Please enter your comment!
Please enter your name here