Differentiate between SUM, SUMIF, SUMIFS and DSUM functions

Whenever you enter a function = SUM in a cell in Excel, you will get a lot of functions starting with SUM and wondering how they are different? This article will help you solve that question.

1. 3 ways to calculate totals in Excel
2. Calculate the subtotal of the list on Excel
3. Calculate the total value of the filtered list in Excel

In this article we will cover four basic functions that are SUM, SUMIF, SUMIFS and DSUM.

Each function has its own function to synthesize numbers, we will learn each function one by one.

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 1

To better understand the function of each function, the article uses an example of a company's sales figure. This is a 1000-row data table, so the range of data will be from cell A1 to cell J1001.

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 2

SUM function

The SUM function functions to sum multiple individual cells, sum a range or even multiple ranges at once.

For example, sum all the number of sold cells (column I), using the following formula:

= SUM (I2: I1001)

Calculate the total selling price (column J), using the formula:

= SUM (J2: J1001)

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 3

SUMIF function

The SUMIF function performs summing based on a condition.

For example:

Sum up all the number of blue boxes sold, using the formula:

= SUMIF (G2: G1001, 'blue', I2: I1001)

Formula of SUMIF function:

= SUMIF (Range, Criteria, Sum_Range)

Inside:

1. Criteria: is the condition for the function, in this example is blue. Conditions are not case sensitive, so you can write blue or BLUE, the function will still understand the conditions you give. Because the condition is text, day, it needs a sign '' . If the condition is numeric, you don't need this sign anymore.
2. Range is the range containing criteria, in this example the range contains Blue, ie column G (Color).
3. Sum_Range is the sum of the sum. In this case we need to calculate the total number so the range here is column I (Qty).

One thing to keep in mind is that the function order is not disturbed, first is Range, Criteria, and finally Sum_Range.

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 4

1. How to combine Sumif and Vlookup functions in Excel

SUMIFS function

The SUMIFS function (note 's' in the function name) is summed based on multiple conditions, unlike the SUMIF function above that can only be summed based on a condition. You can also use this function with only one condition.

For example:

Summing up all the small, blue sized cells we will use the formula:

= SUMIFS (I2: I1001, G2: G1001, 'blue', F2: F1001, 'small')

Formula of SUMIFS function:

= SUMIFS (Sum_Range, Range 1, Criteria 1, Range 2, Criteria 2, ., Range n, Criteria n)

Inside:

1. Sum_Range is the sum of the sum of SUMIFS functions set first. This is different from SUMIF function.
2. Followed by Range and Criteria of each condition, similar to SUMIF.

All conditions in SUMIFS are conditions And, not conditions Or. Therefore, there are no two conditions for the same column. If you do so, the formula will be a little different, we will learn in the next section.

Similarly, you must not disturb the order of functions.

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 5

As mentioned above, you cannot use two conditions in the same column but if you want to calculate the total number of green and yellow cells (in the same column G), we use the following formula:

= SUMIFS (I2: I1001, G2: G1001, 'blue') + SUMIFS (I2: I1001, G2: G1001, 'yellow')

According to the above formula, we combine two SUMIFS functions to facilitate Or. You can use a combination of two SUMIF functions to calculate the sum.

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 6

DSUM function

Now, if you need to calculate the number of blue, black, yellow and pink cells, how to use the formula? You can use SUMIFS + SUMIFS + SUMIFS + SUMIFS function as above.

However, what if you want to calculate the total of 20 colors in the 40-color box in the box?

Instead of writing = SUMIFS + SUMIFS + SUMIFS + SUMIFS + . . 20 times, you simply need to use DSUM function. DSUM function allows you to create conditions outside the formula.

Let's take an example for you to understand this function better.

To calculate the total number of blue, yellow and black cells, create conditions outside the data table.

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 7

You need to write the column header first, in this case Color . Below you write each condition to be summed in each row. When writing this condition under the other condition, you are creating conditions Or.

The formula will be:

= DSUM (A1: J1001, I1, M21: M24)

General formula for DSUM functions:

= DSUM (Database, Field, Criteria)

Inside:

1. Database: is the entire scope of data. In this case, A1: J1001.
2. Field: is the title of the column to be summed, in this example is column Qty (I1).
3. Criteria are the range of data you create outside. Here is the range from cell M21 to M24.

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 8

Example 2:

To calculate the total number of green, small, and large black cells, similar to the above we need to create external conditions.

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 9

When writing criteria side by side means you are facilitating And, writing this criterion below the other criterion means facilitating Or.

The formula used to calculate the total according to the above conditions is:

= DSUM (A1: J1001, I1, M28: N30)

Differentiate between SUM, SUMIF, SUMIFS and DSUM functions Picture 10