How to use SUM Function in Excel?

by

In this tutorial, we will discuss how to use the SUM function. In addition, we include several ways to use this function, such as handling errors in cells, adding up the nth row, and using custom functions.

Step-by-step Guide to Use the SUM Function in Excel

  1. Open the Excel workbook that contains the data you want to add.
  2. Select the range of cells that you want to add. This can be a continuous range or a non-contiguous range of cells.
  3. Go to the “Formulas” tab on the ribbon, and then click the “AutoSum” button. This is the button that looks like the Greek letter sigma (∑), and it’s usually located in the “Editing” group.
  4. A dialog box will appear where you can select the range of cells that you want to add. If Excel correctly detects the range you want to sum, press enter or click ok
  5. Alternatively you can use the function =SUM() directly in a cell, then put the range inside the parenthesis or put selected cells directly.
  6. Once you have entered the SUM function, the sum of the selected cells will appear in the cell where you entered the function.

Summing Values in the Nth Row

In the same example, we want to add the values of every third day. To do this, we will use a combination of ROW, MOD, and SUM functions to get the data:

  1. Open the Excel workbook that contains the data you want to add.
  2. Use the ROW function beside the data, to identify the row number of the data. This will allow you to only select the data you want to sum.
  3. Next, use the MOD function to find the third day of the month. This is done by dividing the row number by 3 and then using the MOD function to find the remainder. For example, =MOD(ROW(),3). This formula will give you the remainder of the row number divided by 3.
    Note: If you want to find a number every n days, replace the divisor by the number you want. (e.g. Every 5 days, =MOD(ROW)(),5) )
  4. Now that we have identified the remainder of each value, we can then find the common remainder for each third day, which is “1.”
  5. Using that information, we can then proceed to create the formula: =SUM(B2:B10*(MOD(ROW(B2:B10),3)=1)). This tells Excel to add all the values from B2:B10 if the value of the remainder from dividing the ROW value by 3 is equal to 1.
  6. The result of this function is the sum of every third day of the month.

You could do the same in finding every third day, starting the count by the first day (Feb 1, 4, & 7) by replacing the remainder value of 1 to 2:

Summing the Largest Numbers

In the same example, we want to find out the sum of the three largest numbers in the data. To do this, we will use the SUM function in conjunction with the LARGE function. In a new cell, enter the formula =SUM(LARGE(B2:B10,{1,2,3})). This tells Excel to add all the 1st, 2nd, and 3rd largest numbers in the cells A2 to A10.

Note: If you want to find the nth largest number, simply change the value in the “k” syntax in the LARGE function. E.g., the 5th and 7th numbers. =SUM(LARGE(B2:B10,{5,7}))

Using SUM to create a Running Total or Cummulative Sum

In the same example, we want to get the sum that accumulates every day. To do this, we use the SUM function and the locking feature of Excel.

The locking feature of Excel makes the autofill function use the same set of data. To do this, we simply highlight the data we want to lock and press F4. This will automatically insert dollar signs ($) into the formula, locking it.

Continue copying and pasting the formula into the remaining cells, creating a cumulative sum.

This makes it so that the starting point of the sum range will always start at B2, while the end of the sum range will change when dragged down.

Cells with no data. In the same example, we will add more dates but no data, while also using the cumulative sum. However, the cumulative sum still shows the amount even if there’s no data, and we want to hide the total.

To do this, we will use the IF function. The formula should be: =IF(B2<>””, SUM(B$2:B2),””). This tells Excel that if the cell B2 is not equal to 0, compute the formula; otherwise, the value is 0.

Summing Data with Errors

In this example, we have data that has numbers and errors in different cells. We want to find the sum of all the numbers while also disregarding the errors in range. To do this, we will use the SUM and IFERROR functions:

  1. First, select the range of cells that contains errors.
  2. Next, use the IFERROR function to replace the “value” with the range of cells you selected to 0: “=IFERROR(A1:A7,0)”. This tells Excel to replace all errors in A1 to A7 with 0.
  3. Then, combine the SUM and IFERROR functions to add up all the numbers in A2:A7. The formula should be: =SUM(IFERROR(A1:A7,0))
  4. The final formula will return the sum of the range of cells, with all the error values replaced by 0.

Conclusion

The SUM function in Excel is a powerful tool that can be used to quickly calculate the total of a range of cells. It can be used in various ways, such as finding the sum of the largest numbers, summing with error values, and creating running totals. It can be used in combination with other functions such as IF, MOD, LARGE, and many others, making it a versatile option for calculating totals in different scenarios.

Related Posts: