Welcome! Register Now to Unlock Exclusive Excel TutorialsGet Started
- Exclusive Playlists
- Downloadable Videos
- No Ads!
- Exclusive Templates
Description: In this video, we will learn how to use sum functions in Excel. We will also learn what the different criteria are and how they can be used when solving complex sum situations in Excel. In addition, you will see examples of each type of sum function so that you know which one to use in a given situation. This video is for anyone who wants to maximize their knowledge about SUM formulas and want to apply it in many real world scenarios! Table Of Content: 00:00 Introduction 01:27 Part 1 - SUM Basics 04:30 Part 2 - SUM Nth Row 09:50 Part 3 - SUM Largest Numbers 12:16 Part 4 - SUM With Errors 14:05 Part 5 - Running Total Or Cumulative SUM 19:50 Conclusion First Lets see the Syntax of the SUM function in excel; SUM(Range) In the example below, we are summing up all the numbers in the range A:B. This is what is known as a simple SUM. =SUM(A:B) However, Excel offers more flexibility than just this basic SUM function. In the next few parts of this video, we will look at some of these more complex formulas. Part One: SUM Basics In this part, we will take a look at the different criteria that can be used with the SUM function in Excel. The first criterion is whether you want to sum all the numbers or just some of them. To sum all the numbers in a range, select the range and then type in =SUM(A:B) into a blank cell. Part Two: SUM Nth Row To sum up only every n-th number, you can use the MOD function to get rid of all the numbers except for every n-th one. In order to do this, select A:B and type in =SUM(MOD (A:B, ROW (INDIRECT("n"))-COLUMN (INDIRECT("n"))) to use the nth number as reference. The result will be a sum of every other row starting with the first one. Part Three: SUM Largest Numbers In this part, we will look at how to sum the largest numbers in a range. To do this, you can use the LARGE function. This function takes three arguments: the first is the range of cells that you want to find the largest number from, the second is the position of the number within that range (starting from 0), and the third is the criteria that you want to use to determine which number is the largest. For example, if we wanted to find the largest number in cell A14, we would use the following formula: =LARGE(A:A, 14, FALSE) This function will return the value in cell A14 as the largest number in the range A:A. Part Four: SUM With Errors In some cases, you may have cells with errors in your data set. This can cause problems when trying to sum up a range of cells. However, Excel has a function called IFERROR that can help us out in these situations. For example, if we wanted to sum up the numbers in A:B and ignore cells with errors (e.g., #DIV/0! or NA), we would use =SUM(IFERROR(A:B)) as our formula. This will tell Excel that when it encounters a cell with an error value, just ignore it. Part Five: Running Total Or Cumulative SUM As the names imply, these two criteria are used to determine whether you want Excel to sum up all of your numbers or just the ones that meet your conditions (e.g., every n-th number). To use either one in a formula, select A:B and type in =CUMULATIVE SUM(A:B) for a running total or =CUMULATIVE SUM(IFERROR(A:B), A:B) for a cumulative sum that ignores errors. In the example below, we are using the CUMULATIVE SUM function to find the total of all numbers in column A. Conclusion In this video, we looked at the different criteria that can be used with the SUM function in Excel. We saw how to sum all of the numbers in a range, sum every n-th number, find the largest number in a range, and sum up cells with errors. Additionally, we learned how to use Excel's IFERROR function to ignore cells with errors. Finally, we saw how to find the running total or cumulative sum of a range of cells.