Whenever you type =SUM in a cell in Excel, you get a lot of functions starting with Sum…

In this blog post, we will be focussing on 4 of them – SUM, SUMIF, SUMIFS, DSUM.

Sum 4 functions

Sum 4 functions

Each of these functions has its own capability for Summing up numbers. Let’s go one by one.

We will be using a sample data set of Umbrella sales (1000 rows of data), to understand each of these functions in detail. Thus the data range is, A1 : J1001.

Umbrella Sales Data

Umbrella Sales Data

SUM

Sum function will get you a sum from multiple individual cells, a single range, or even multiple ranges in 1 shot.

Sum of all Quantity (I column)

The formula will be =SUM(I2:I1001)

Sum of all Totals (J column)

The formula will be =SUM(J2:J1001)

sum-function

SUMIF

Sumif function gets you a sum based on 1 condition (only).

Sum of all Quantities of only Blue umbrellas

The formula will be =SUMIF(G2:G1001,”blue”,I2:I1001)

=SUMIF (Range, Criteria, Sum_Range)

  • Criteria – Blue (the actual condition).
    • The criteria is not case sensitive. The “blue” can be written in upper case or small caps, doesn’t matter.
    • Since the criteria is text, it needs the ” “. If the criteria was a number, it wouldn’t require the ” “. Date will require ” “.
  • Range – The column where Blue, the criteria, resides. Thus Color (G) column in this case.
  • Sum_Range – Sum of what? Quantity, Total? since we need the Sum of Quantities, we choose Qty (I) column.
  • The order can’t be messed up – First the Range, then Criteria, then the Sum_Range

sumif-function

SUMIFS

Sumifs (notice the ‘s’) gets you a sum based on multiple conditions. But it also works with a single condition as well!

Sum of all Quantities of Blue, Small size Umbrellas

The formula will be =SUMIFS(I2:I1001,G2:G1001,”blue”,F2:F1001,”small”)

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

  • Observe carefully that in the case of SUMIFS, the Sum_Range comes first!
  • Following after, Range & Criteria of each condition, similar to SUMIF
  • Note that all conditions in SUMIFS will always get ANDed and never ORed
  • Thus, no 2 conditions should fall in the same column. If they do, your formula will be a little different, explained below in the next example.
  • The order can’t be messed up – Sum_Range first!
  • As stated above too, SUMIFS is capable of handling a single condition too. I thus never use SUMIF, i only use SUMIFS.

sumifs-and-ing-criteria

Sum of all Quantities of Blue + Yellow Umbrellas

The formula will be =SUMIFS(I2:I1001,G2:G1001,”blue”)+SUMIFS(I2:I1001,G2:G1001,”yellow”)

  • Observe that in this case, both the conditions are falling into the same Color column.
  • Thus, both conditions need to be ORed.
  • This can easily be done by using
    • SUMIF + SUMIF      OR
    • SUMIFS + SUMIFS

sumifs-or-ing-criteria

DSUM

Now, what if you needed to have a summation of quantities of Blue, Black, Yellow and Pink colors!

What is there are 40 different colors of umbrellas that you are selling, and you want a total of 20 of those colors!

Instead of writing =SUMIF + SUMIF + SUMIF + SUMIF + ….. 20 times, simply use DSUM !!!

=DUM let’s you create your condition outside the formula, making it very dynamic in nature, and easy to create.

Sum of quantities of Blue + Yellow + Black umbrellas

Creating the Condition (Criteria) outside

dsum-criteria-outside-1

DSUM criteria outside

Write the column Header first (exactly same as in the data), in this case ‘Color’.

Below that, write each of the colors, one below the other.

When you write the criteria’s one below the other, it means OR-ing.

The formula will be =DSUM(A1:J1001,I1,M21:M24)

=DSUM ( Database, Field, Criteria )

  • Database – Select the entire data set
  • Field – Select only the header of the column, of whose SUM you want, in this case the Quantity column, hence I1.
  • Criteria – Select the grid that have you created outside.

dsum-for-or-ing

Sum of quantities of (Blue, Small) + (Black, Large) umbrellas

Creating the Condition (Criteria) outside

dsum-criteria-outside-2

When you write the criteria’s next to each other, it means AND-ing, and below means OR-ing.

The formula will be =DSUM(A1:J1001,I1,M21:M24)

dsum-for-and-ing

 

Read more…

CountIF and SumIF functions in Excel for awesome calculations

How to use AND in IF formula in Excel