In this lesson, we will not consider how to calculate the sum in Excel using the addition operator, autosum, and other tools. Today we will analyze only two functions: SUM и SUMMESLI. I hasten to please you, their functionality is enough to solve almost all possible summation issues in Excel.
SUM Function – Simple Sum of Cells in Excel
Function SUM calculates the sum of all its arguments. It is the most commonly used function in Excel. For example, we need to add the values in three cells. Of course, we can use the usual summation operator:
But we can also use the function SUM and write the formula as follows:
Since the function SUM supports working not only with individual cells, but also with entire ranges, then the above formula can be modified:
The true power of a feature SUM is revealed when you need to add a large number of cells in Excel. The example below requires 12 values to be summed. Function SUM allows you to do this with a few mouse clicks, but if you use the addition operator, then you will have to fumble for a long time.
In the following example, the function SUM adds the entire column A, and these are 1048576 values:
The following formula calculates the sum of all cells contained in the worksheet Sheet1. This formula must be used on a different Excel worksheet (other than Sheet1) to prevent this formula from causing a circular error.
Function SUM can take up to 255 arguments and sum multiple non-contiguous ranges or cells at once:
If the summed values contain text, then the function SUM ignores them, i.e. does not include:
If you try to add text values using the summation operator, then the formula will return an error:
Function SUM is quite versatile and allows you to use as its arguments not only references to cells and ranges, but also various mathematical operators and even other Excel functions:
If you are interested in learning how to calculate the cumulative sum in Excel, refer to this lesson.
SUMIF – conditional sum in Excel
Function SUMMESLI allows you to calculate the notional amount in Excel, i.e. the sum of cells that meet a certain criterion. Function SUMMESLI can contain only one criterion.
For example, the following formula sums only positive numbers in the range A1:A10. Note that the condition is enclosed in double quotes.
You can use a cell value as a condition. In this case, changing the condition will change the result:
We change the condition, the result also changes:
The condition can be combined using the concatenation operator. In the example below, the formula will return the sum of the values that are greater than the value in cell B1.
In all the examples given earlier, we summed up and tested the condition on the same range. But what if you need to sum one range, and check the condition in a different way?
In this case, the function SUMMESLI saved the third optional argument, which is responsible for the range to be summed. Those. on the first argument, the function checks the condition, and the third is subject to summation.
In the following example, we will sum up the total cost of all fruits sold. To do this, we use the following formula:
Pressing Enter we get the result:
If one condition is not enough for you, then you can always use the function SUMMESLIMN, which allows you to conditionally sum in Excel based on multiple criteria.
Summarization is one of the main actions that a user performs in Microsoft Excel. Functions SUM и SUMMESLI created to facilitate this task and give users the most convenient tool. I hope that this lesson helped you master the basic sum functions in Excel, and now you can freely apply this knowledge in practice. Good luck and success in learning Excel!