Difference Between SUMIFS and DSUM in Google Sheets

0
148
DSUM VS SUMIFS

Normally I write tutorials specifically for Google Sheets but many of them are applicable to Excel also. So the same case is relevant this time too. Now back to our tutorial. I will explain the difference between SUMIFS and DSUM in Google Sheets and then elaborate it with the help of a simple easy to follow example.

You may wonder why I skipped SUMIF, another similar function to SUMIFS and DSUM. The reason is simple. SUMIFS and DSUM can handle multiple criterion. SUMIF has limitation.

First let us take a look at what are the difference between SUMIFS and DSUM. Then we can move to the example.

Difference Between SUMIFS and DSUM in Google Sheets

Both SUMIFS and DSUM can give identical results. Then what is the difference between SUMIFS and DSUM?

  • DSUM is a database function. The ‘D’ in DSUM represents database on the other hand SUMIFS is a conditional function. The ‘IFS’ in SUMIFS stands for multiple conditions.
  • DSUM require your data well organised under field titles or labels. In other words a proper database is required. DSUM finds results based on given criterion from whole database.
  • In SUMIFS you can select data from column ranges. So column title or label is not necessary. You need to include column ranges individually in the formula not as a whole.
  • In DSUM, the criterion normally comes outside the formula and it should be entered under field titles or labels matching the database and this also should select as a whole. The benefit, formula looks so clean. So in DSUM we just need to concentrate on the criteria part. If you master the DSUM criteria, you are conquered DSUM! You can of course enter criterion inside the formula too. It behaves just like an array so you need practice to do so. Normally people skip this.
  • On the other hand SUMIFS can call criterion from outside the formula, or if you wish you can directly enter criterion inside the formula in the same way. SUMIFS formula looks cluttered, but offers great flexibility.
  • Both SUMIFS and DSUM can include multiple criterion.
  • If you use DSUM, in a future date you can easily follow the formula used. But SUMIFS may make you confused.

Example to Understand the Difference Between DSUM and SUMIFS

The example only to make you understand the difference of the above two awesome functions. To know how to use them, you can find it under our spreadsheet section HERE.

Sample data to find difference between SUMIFS and DSUM

From the above data I am going to sum the “Sales Value” of “Philip Nida” based on the below other conditions.

Area: Both North and South, Sales Date between 01/07/2017 to 31/07/2017. First see the DSUM Criterion and DSUM function below.

DSUM complex criterion for SUMIFS comparison

Below is the function used.

=dsum(A6:D14,4,A2:D4)

Now for SUMIFS the above criterion can be included within the formula as below, little bit lengthy.

=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))

Both DSUM and SUMIFS can produce same result. See the formula. DSUM is too short. It’s up to you which one to choose.

LEAVE A REPLY

Please enter your comment!
Please enter your name here