I received several emails asking me to compare Sumifs, Sumproduct, and Dsum with an example. It seems that many Google Sheets users are confused by these similar functions. I have already written tutorials on how to use SUMIFS, SUMPRODUCT, and DSUM in Google Sheets with proper examples on my website. However, I have not yet compared these functions with examples. In addition, I have done a detailed comparison between Sumifs and Dsum, as well as between Sumifs and Sumproduct. Now, let’s compare Sumifs, Sumproduct, and Dsum with examples for a quick review.
Sample Data to Compare Sumifs, Sumproduct, Dsum with Example
To illustrate the comparison, I will use a formula with multiple criteria. This formula includes a date criterion, the difference between two dates, and checking the same range twice with different criteria. Now let’s look at the formula to be used to compare Sumifs, Sumproduct, and Dsum.
The Formula to Compare SUMIFS vs. SUMPRODUCT vs. DSUM
The following formula will check for data that meets the following criteria: “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. The formula will then sum the values in column D that match the given criteria.
SUMIFS
=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
The DSUM formula may look cleaner because it uses criteria from outside the formula. However, it can also take criteria inside the formula, which is a bit tricky. I will explain this in a later tutorial.
Now it’s your turn to check the formulas provided above and decide which one is better: DSUM, SUMPRODUCT, or SUMIFS. I am also interested to know your choice, so please let me know in the comments below.
Remember, for more useful articles and guides on Google Sheets, visit Crawlan.com.
