Compare Sumifs, Sumproduct, Dsum with Example in Google Sheets

0
186
formula sumifs vs dsum vs sumproduct

I got few mails asking me to compare Sumifs, Sumproduct, Dsum with example. I also think it’s now necessary as many of the Google Sheets users get confused with these similar types of Google Sheet functions.

In this site I have already written few tutorials on how to use SUMIFS, SUMPRODUCT as well as DSUM in Google Sheets that with proper examples. But I did not compare sumifs, sumproduct, dsum with example so far.

Also I’ve done a detailed comparison between functions sumifs and dsum. Similarly done another feature comparison between sumifs and sumproduct.

Now I think I should compare sumifs, sumproduct, dsum with example as it will be useful for a quick review. The above two examples are for you to choose between the functions. But this post is to make you understand how the functions can be used in similar data.

formula sumifs vs dsum vs sumproduct

Sample Data to Compare Sumifs, Sumproduct, Dsum with Example

I am using a multiple criterion formula with the example. So in the formula I have used date criterion, date difference between two dates, check same range twice with different criteria like techniques.

Compare Sumifs, Sumproduct, Dsum with Example - Sample Data

Now to the formula to be used to compare sumifs, sumproduct and dsum.

Formula to Compare SUMIFS Vs SUMPRODUCT Vs DSUM

What the below comparison formula is going to check? It will check for data fallen under the following criteria.

As mentioned above I used multiple criteria in this formula. It looks for “sales_person” named “Philip Nida” in column A, area “North” or “South” in column B, and “sales_date” between “01/07/2017” and “31/07/2017” in column C. Then the formula will sum the value in Column D matching the conditions given in the criteria.

SUMIFS

=sumifs(D2:D9,A2:A9,“Philip Nida”,B2:B9,“North”,C2:C9,“>=”&date(2017,7,1),C2:C9,“<=”&date(2017,7,31))+
SUMIFS(D2:D9,A2:A9,“Philip Nida”,B2:B9,“South”,C2:C9,“>=”&date(2017,7,1),C2:C9,“<=”&date(2017,7,31))

SUMPRODUCT

=sumproduct((A2:A9=“Philip Nida”)*((B2:B9=“North”)+(B2:B9=“South”))*(C2:C9>=DATE(2017,7,1))*(C2:C9<=DATE(2017,7,31))*(D2:D9))

DSUM

=dsum(A1:D9,4,A12:D14)

Criteria only for DSUM formula

sample criteria outside formula in DSUM

In the above formula DSUM looks clean because it uses criteria from outside the formula. It can also take criteria inside the formula. It’s little bit tricky. I will detail it in a later tutorial. Now check the above mentioned formulas to know which formula is better – DSUM or SUMPRODUCT or SUMIFS. I am also interested to know which one you opt in the comments below.

LEAVE A REPLY

Please enter your comment!
Please enter your name here