Excel is a powerhouse of various tools and assets tailored for data analysis and manipulation. A familiar form of data manipulation is addition. A handy Excel function for adding numbers up, is the SUM function. Thence, SUM is perhaps the first function that every Excel user learns.
Although SUM is the head of its family, it's not the only SUM function in Excel. Several different SUM functions are available in Excel, each with its unique spice. This article will discuss the essential SUM functions in Excel and how to use them.
The SUM Functions in Excel
The members of the SUM family have two things in common: First, they all sum values in one way or another, and second, they all have SUM in their names.
The first function, SUM, simply sums the input values and outputs the results. The other SUM functions do the same but with some twists along the way. For instance, SUMIF only adds values that meet a particular condition. SUMPRODUCT performs an arithmetic operation on the arrays and then sums the results.
Although all SUM functions do indeed sum, that's not the only thing that they do. An understanding of the different SUM functions can significantly boost your productivity. You won't have to look into combining SUM with other Excel functions, but instead, use a SUM function that perfectly suits your needs.
Let's explore the essential SUM functions along with examples of how to use each one.
1. SUM
The SUM function is the most basic member of the SUM family in Excel. You can use it to add a range of numbers together. The syntax for the SUM function is:
=SUM(cells)Wherecells is the cells, or range of cells, that you want to sum.
For example, the following formula would sum the values in cellsA1 toA10:
=SUM(A1:A10)2. SUMIF
You can usethe SUMIF function to sum up a range of numbers that meet certain criteria. SUMIF basically combines the SUM and IF functions together. The syntax for it is:
=SUMIF([sum_range], criteria, [criteria_range])Wheresum_range is the range of cells you want to sum,criteria is the condition you want to use to filter the values, andcriteria_range is the range where the function should look for the condition.
For example, the following formula would sum all the values in cellsA1 throughA10 that are greater than50:
=SUMIF(A1:A10, ">50", A1:A10)In this example, the sum and criteria range are the same. However, these two arguments can also be two distinct ranges:
=SUMIF(A1:A10, ">10", B1:B10)The formula above sums the cells inA1 throughA10 only if the adjacent cell is larger than10. You can also use text conditions with SUMIF.
3. SUMIFS
The SUMIFS function is similar to the SUMIF function. But where SUMIF only takes in a single criterion, SUMIFS can take in multiple criteria.
The syntax for the SUMIFS function is:
=SUMIFS([sum_range], criteria1, [range1], criteria2, [range2], ...)Wheresum_range is the range of cells you want to sum,criteria1 is the first condition you wish to use to filter the values,range1 is the value you want to use to filter the values forcriteria1, and so forth.
For example, the following formula would sum all the values in cellsA1 throughA10 that are greater than50 and less than60:
=SUMIFS(A1:A10, A1:A10, ">50", A1:A10, "<60")In this example, the sum and both criteria ranges are the same. Much like the SUMIF function, these arguments can refer to different ranges:
=SUMIFS(A1:A10, B1:B10, "=Black", C1:C10, "=Slim")The formula above sums up the count of items that are Black and Slim. It looks for the first and second conditions inB1:B10 andC1:C10, respectively.
Note that SUMIFS does not sum the cells that meet the first or second criteria. Indeed, it sums up the cells that meet both the first and second criteria.
4. SUMPRODUCT
The SUMPRODUCT function multiplies a range of numbers and then sums the products. The syntax for the SUMPRODUCT function is:
=SUMPRODUCT(array1, array2, ...)Wherearray1 is the first array of numbers andarray2 is the second. SUMPRODUCT multiplies the first cells of the arrays together, then the second cells, and so forth.
If you've placed the two arrays adjacent to each other, then SUMPRODUCT will multiply each cell by the cell adjacent to it. Finally, it sums the products together and outputs the result.
For example, the following formula would multiply the values in cellsA1 toA10 with the cellsB1 toB10 and then sum the products:
=SUMPRODUCT(A2:A11, B2:B11)
You can also have SUMPRODUCT perform arithmetic operations other than multiplication. To do this, replace the comma between the arrays with the operation's symbol (+,-,*,/).
For example, the formula below divides the arrays together and then sums the results:
=SUMPRODUCT(A2:A11 / B2:B11)In more advanced formulas, you can use the double-negative (--) to have SUMPRODUCT operate on specific values. Consider the formula below:
=SUMPRODUCT(-- (B2:B8="Red"), C2:C8, D2:D8)
This formula takes in the cells in columnsC andD only if their adjacent cell in columnB equalsRed. Then it multiplies the two arrays together, and finally sums the results. The output of this formula is the total sales of Red items.
5. SUMSQ
The SUMSQ function sums the squares of a range of numbers. The syntax for the SUMSQ function is as follows:
=SUMSQ([cells])Wherecells is the range of cells you want to sum the squares of.
For example, the following formula would sum the squares of the values in cellsA1 throughA10:
=SUMSQ(A1:A10)6. IMSUM
This function is a lesser-known member of the SUM family, as it deals with a specific set of numbers. The IMSUM function sums complex numbers. Complex numbers, which consist of a real part and an imaginary part (expressed as 'a + bi' where 'a' and 'b' are real numbers and 'i' represents the square root of -1), are the domain of the IMSUM function. You probably shouldn't be using this function if you didn't know that.
The syntax for the IMSUM function is as follows:
=IMSUM([range])Whererange is the range of cells that you want to sum. Note that IMSUM only works on complex numbers.
For example, the following formula sums the complex numbers in cellsA1 throughA3:
=IMSUM(A1:A3)The Sum of Excel's Greatness
The SUM function is one of the most basic yet essential functions in Excel. It performs a simple task: takes in the input values, and adds them together. However, SUM isn't alone in its family. There are various deviations of it, each serving a unique purpose.
Now that you know about the rest of the SUM functions, you can skip combining SUM with other Excel functions and use an apt function instead. The more Excel functions you learn and master, the faster you'll come up with creative formulas for specific scenarios.
