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.

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

In our last post we saw SUMIF in which we can ADD range of cells based in 1 Condition, however what if we had multiple conditions. For e.g. we have to ADD Salary of “Adam” for month of January Only.

We can use SUMIFS, as the suggest we can add the range of cells that’s meet our multiple criteria’

Syntax:  SUMIFS (sum range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

DIFFERENCE BETWEEN SUMIF AND SUMIFS

SUMIF SUMIFS
One Condition/Criteria Multiple Criteria (Maximum 127 Criteria)
Sum Range is last Argument Sum Range is first Argument
Sum Range is Optional if Sum Range and Criteria Range is same Sum Range is Mandatory though both range are same.

 The SUMIFS function syntax has the following arguments:

Sum Range (Mandatory). The range of cells for that needs to be added.

Criteria Range 1(Mandatory). It is the Criteria or Condition, based on which you want Sum.

Criteria 1 (Mandatory). It is your Criteria range (Range of Cells).

Criteria Range 2, Criteria 2…. (Optional). Additional criteria ranges and their associated criteria. You can enter up to 127 range/criteria pairs.

Note: If multiple criteria is from same column then we have use SUMIFS multiple times. '=SUMIFS (E2:E18, C2:C18,"Accounts", B2:B18,"01/01/2016")+
SUMIFS (E2:E18, C2:C18,"Accounts", B2:B18,"03/01/2016)"
(Check third example in below pic)

Wild Characters (You can read Wild Cards in detail in our last post On SUMIF)

  • Question Mark (?): Matches single character (Bob, Bab becomes B?b)
  • Asterisk (*): Matches multiple characters (Adam, Annam becomes A*m)
  • Tilde (~): When we have use actual question mark or asterisk as a criteria (Adam? Annam? Becomes A*m~?)Table
    SUMIFS-E1

SUMIFS-E2

SUMIFS-E3

DatesExplanation