Which & how to use SUM functions in MS Excel

MS Excel is a powerful and one of the most extensively used tool in any professional job. This blog covers the different types of SUMs that can be used, when they can be used and why.

SUM

This is the most basic sum function. It returns the sum of values in a set of cells or ranges.

Below are some of the ways SUM can be used

• SUM over a single range, this formula returns the total sum of values in cells A1 to A4.

=SUM(A1:A4)
result => 20

A

2

3

5

10

• SUM over a multi cell range, this formula returns the total sum of values in cells A1 to C4.

=SUM(A1:C4)
result => 90

A	B	C
2	15	13
3	8	17
5	2	6
10	5	4

• SUM of multiple cells, this formula returns the sum of values in cells A1, B2 & C5.

=SUM(A1,B2,C4)
result => 10

A	B	C
5	1	0
6	3	6
3	4	3
7	8	2

• SUM over combination of cells & ranges, this formula returns the sum of values in cells A1, B2 and range C3 to D10. It acts as a combination of 2nd and 3rd sum formulas above.

=SUM(A1,B2,C3:D10)
return=> 40

A	B	C	D
4	7	1	0
3	2	9	4
8	2	8	12
9	4	10	4

SUMIF

As the name indicates, SUM only IF a condition is satisfied. The condition here acts as a filter, any value not satisfying the condition will be excluded.

=SUMIF(B1:B4,"ok",A1:A4)
result => 5

A	B
2	OK
5	NA
3	OK
6	NOT OK

This function is best when we need to check the sum of values for a category from a large data set. If we have a large data set with 10,000 line items and 10-20 different categories. Using the SUMIF function gives the flexibility to find sum for any category quickly.

Limitation of this function is that it can only check for one condition.

SUMIFS

This function works same as SUMIF but gives the flexibility to add more than one condition, overcoming the limitation of SUMIF.

=SUMIFS(A1:A4,B1:B4,"OK",C1:C4,">5")
result => 4

A	B	C
2	OK	1
4	OK	6
3	NA	8
5	OK	2

In the above table only one transaction/line item satisfy both the conditions hence result was 4. The conditions in formula works with AND logic i.e. it is mandatory for all the conditions to be satisfied.

Now which one to use in which case?

SUM is best when you want just a sum of values, just put it in place and forget about it. Best case is when a column contains only single category values.

SUM is best when we need to calculate what is the total amount/total quantity sold for the day.

Product	Quantity	Amount
Coffee	2	120
Tea	5	100
Cold Coffee	1	80

Now every time data is changed, this formula will give the total of the assigned range.

SUMIF and SUMIFS are good to use when we have a condition to satisfy and by making the formula dynamic by using the relative reference for condition, formula can be replicated for multiple conditions.

=SUMIFS(C1:C3,A1:A3,E4)
result will depend on the value specified in cell E4, if value in E4 is coffee, the result will be 120.

Here the cell E4 contains the category "coffee" hence the total amount returned will be 120. If we need to check the total amount for Tea then we can change the value in cell E4 to Tea and we will get the desired result, in this case it will be 100.

Thanks for reading.

If you have any doubt or query, do write back and we will try to SOLVE THAT QUERY.