Summing cells in Excel is one of the most commonly used operations. It is used by accountants and financiers to obtain totals of income and expenses, sales companies to obtain work results and calculate statistics. Let's get acquainted with the ways in which you can solve all these problems.
Addition in Excel
The determination of the sum of elements in the program is possible using the simplest arithmetic operations, which are indicated by the “+” sign. To add a small number of terms, this method is best suited.
For example, to calculate the total cost of a tourist application, which includes hotel prices, transfers and excursions, you can use the addition operation using the formula. The formula is entered in the cell after the equal sign ("="). To select the cells included in the amount, just click on the desired one, and its location will be indicated in the formula.
Finding Amount
The next step is to find the sum of the columns. In the example already given, we set the task of calculating the total income from the sale of all applications, including payment for hotels, transport and sightseeing programs. To do this, summarize all the fields in the "Total Cost" column. Using the arithmetic sum here is inconvenient, since the number of entries in the table can be very large.
For such situations, the Excel SUM function () is suitable. It adds the numbers in the specified range of cells without the need to prescribe the location of each field: SUM (number1; [number2]; ...), where the range of numbers in the cells can be specified as the argument "number". The function accepts no more than 255 arguments. In the travel agency application example, it will be applied to column I:
Conditional summation
There are tasks in which it is necessary to find the sum of not all elements of the table in a column or range, but only those that meet the requirements. In such cases, the user needs the ability to specify the conditions for including the number in the calculation. The SUMMES function is intended for this.
Its syntax is:
SUMMS (range; condition; [sum_range]).
In the range of fields specified by the argument "range" the specified requirement is checked to add a number to the sum. This attribute and the following “condition” are required for use in the formula. Therefore, in the field "Criterion" we indicate our requirement.
The third parameter of the command indicates the cells from which data will be summed. This field is optional. If it is not specified, the range already specified in the first argument is taken as the range. But these fields may differ if the SUMMS function checks the condition on one column and adds the numbers in another. We show this by example.
Examples of using
We calculate the income only from those tourists who booked individual transfers from us. We indicate the “Transfers” column as fields for checking requirements; the criterion itself is the equality of the field to “Individual”.
And in this case, we should indicate the column of terms in the "Sum_range" field of the SUMMESLES function parameters window:
As a result, we get the amount of data only on applications with individual transfers.
Another example is the use of a command in conjunction with the VLOOKUP formula: VLOOKUP (the desired index; search interval; column number in the interval; exact or approximate equality - 0 (exact) or 1 (approximate)).
In practice, to use VLOOKUP in Excel, a subtable is selected (parameter 2), the first column of which contains the desired value (parameter 1). The command determines the row in which it is contained and, as a result, displays the contents of the field in the found row and the specified column (parameter 3).
Suppose transfers are indicated in the income table not by names, but only by codes, but the names are contained in another table.
To solve the previous problem, we determine the desired one from the list of codes and find it in the column of the income table. To select the desired code, use the VLOOKUP function:
= VLOOKUP (A11; Table 12; 2).
It will return the number 2 - the code of individual transfers. Now we substitute this function into the SUMMESLI parameters window in Excel and get the same value as in the first case.
Amount with several conditions
In a complicated task, it is required to calculate the sum indicating not one, but several conditions. But the described formula only works with one requirement, so we need another. An analog of the previous function is SUMMESLIMN, which stacks the cells, taking into account several restrictions specified by the user.
By analogy with the SUMMES function, in the parameters window of this formula, specify the range of cells for addition and condition. Command syntax:
SUMMER (sum_range; condition_range1; condition1; [condition_range2; condition2]; ...).
Here the summation range is already a required argument. In addition to it, a number of conditions for the search are specified with the selection criteria indicated.
On an example, we calculate the income only from those applications in which transfers are individual, and tourists ordered a sightseeing tour.
Only one application meets these criteria, so the total will be equal to the income on it.
Using the SUMMES function in Excel makes it easy to complete many tasks. Its syntax is simple and accessible for use even by an inexperienced user of the program.