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) – SUMPRODUCT

In our last post, we saw SUMIFS, which is used to ADD Range of Cells using multiple conditions. Similarly, we can use SUMPRODUCT. This function is applicable in Excel versions 1997 and later.

This function is unique because it has multiple uses for example we can use it to ADD ranges of Cells (Array) or to return COUNT of Range of Cells (Arrays) with multiple conditions or to return SUM of Multiplication of 2 adjacent arrays. In addition, we can use multiple criteria from same column in single formula, which is not possible in SUMIFS (We have to use multiple formula to use multiple conditions from same Array)

 SYNTAX: SUMPRODUCT (array1, [array2], [array3], … )

The SUMPRODUCT function syntax has the following arguments:

The SUMPRODUCT function syntax has the following arguments:

  • Array1 (Range of Cells, Mandatory). The first array argument whose components you want to multiply and then add.
  • Array2, array3,   (Range of Cells, [ ] Optional):  Array arguments whose components you want to multiply and then add. (2nd  Array to 255th Array)
Note:
  • All the arrays must have same range size or FUNCTION will return #VALUE! Error( B2:B18, C2:C19 in same function is not allowed)
  • Maximum Arrays can be used is 255.
  • If any cell(s) of Array contains Non-numeric value, SUMPRODUCT returns zero for that particular cell(s).
Operators:
  • Plus (+): It is used when we have multiple criteria from same Array. =SUMPRODUCT(Range1 = “XYZ”+Range1 = “ABC”,Range2) . We have 2 criteria (XYZ and ABC)  from Range1 and Result Range is Range2. it will return sum of all the cells of Range2 where criteria is either ABC or XYZ. Also it converts TRUEs and FALSEs into 1s and 0s.
  • Double Negative(- -): It is used convert TRUEs and FALSEs into 1s and 0s. When we set criteria, function returns TRUE or FALSE depending upon whether criteria is met or not. For example, =SUMPRODUCT(($B$3:$B$18=VALUE(“01/01/2016”)),$G$3:$G$18) will return 0. Because criteria range returns TRUE , in SUMPRODUCT TRUE (Text) by Numeric Value = 0. we use – – in front criteria range =SUMPRODUCT(– –($B$3:$B$18=VALUE(“01/01/2016”)),$G$3:$G$18) which return expected result. Now 1 (Numeric) by Numeric will return expected result.
  • Asterisk (*): It is wildcard as well multiplication operator. if we don’t want to use – – then we can use * instead.=SUMPRODUCT(($B$3:$B$18=VALUE(“01/01/2016”))*$G$3:$G$18). We have used Asterisk in place of comma (which is used to separate criteria).

SUMPRODUCT-Table

SUMPRODUCT-E1

SUMPRODUCT-E2

SUMPRODUCT-E3

To Return COUNT of Cells.

SUMPRODUCT-E4

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

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

Most common function we use in excel is SUM function. A simple SUM function adds the range of cells directly. However, what if we want to add only those cells that matches our criteria or condition. There are few functions in Excel, which I am going to explain.

One of the most used function is SUMIF (SUM with one Condition). This function can be used Excel version 2003 and later.

Syntax SUMIF (range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

  • Range(Mandatory). It is your Criteria range (Range of Cells).
  • Criteria(Mandatory). It is the Criteria or Condition, based on which you want Sum.
  • Sum Range(Optional, [ ] are used to show optional arguments). The range of cells for that needs to be added (Result Range). It is optional if your Criteria range is same as Sum Range.
Note: All text (characters) criteria or Mathematical Symbols (Only Exceptions are Numeric) must be in double quotes ("). For e.g.“>”&15,”>15”, 15, C3,”Bob”or “>01/01/2016”.

You can use the wildcard characters.

  • The Question mark (?): A question mark matches any single character and is use to fill in the spaces of characters you want in criteria. For E.g. Bob, Bab, Bib etc. can be used as “B?b” and you will have results of three criteria in one condition for 3 letter word that starts and ends with “b”.
  • Asterisk (*): An asterisk matches any sequence of characters. It is just like using “contains” in Auto Filter. For e.g. “*ob” (Anything that ends with “ob”), “Bo*” (Anything that starts with “Bo”), “*o*” (Anything that has “o” in the middle).
  • Tilde (~): If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character. For e.g. “Bob?” can be written as “?ob~?” (Anything that has “ob” in middle and “?” as last word in 4 letter word.

If we have 2 criteria from same column then use SUMIF 2 times. Please check second last example in below image.

SUMIF-Table

SUMIF-E1

SUMIF-E2

SUMIF-E3

SUMIF-E4

DatesExplanation