How To Do Sum/Addition Of Cells with Condition(s) – DSUM

In our last post, we saw SUMPRODUCT, which is used to ADD Range of Cells using multiple conditions. In addition, it helps to add cells having multiple criteria from same criteria range. We can also use DSUM function. This function is not used commonly  and is similar to Advanced Filter where we have to specify criteria table range. Confused… right? 🙄 Do not worry I will explain with examples.

SYNTAX: DSUM (database, field, criteria)

The DSUM function syntax has the following arguments:

Database (Mandatory). It is the Table array (Database). First row must be headers.

Field (Mandatory). It is our Result Range (Sum Range in SUMIF). Just enter column header in double quotes for Non-Numeric Column Headers. For example “Salary $” or “Days Worked”. Column Header should be same as Column Header of Table Array (Database). Alternatively, use Column number (Position of Column) in Table Array. For example 1 for first column, 2 for second column etc.

Criteria (Mandatory). It is the Criteria Range. It is similar to Table array only difference is it has only criteria. Minimum one criteria range is mandatory; you can keep criteria cells blank if you do not want any criteria.

Note:
  • At least one criteria range is mandatory with One Header and Criteria cell below it (If we keep criteria cell blank, function will return SUM of all the cells of Sum Range (Field). For example, if the criteria range is M2:M3, M2 should be Column Header that matches with Table Array Header and M3 should contain criteria. If headers does not match then DSUM will return SUM of all cells from Field (Sum Range) in short will not consider conditions set by you.
  • We can place Criteria Table Array(s) anywhere in the Worksheet (Just like Advance Filter) but place where it not intersect with Table Array.
  • Criteria Table Array should not exceed Table Array (Database) both Column and Row wise.
  • If you want SUM of entire column then keep criteria cells blank.
  • We can use Wildcards (*, ?, ~) in criteria range cells.

    SUMPRODUCT-Table
    Table Array

DSUM-E1

dsum-e2

If you have any question regarding this post, please write in comments section.

Leave a Reply

Your email address will not be published. Required fields are marked *