Excel is an indispensable assistant in the work of such specialists as economists, accountants, financiers, managers, technologists and many others. When hiring for these vacancies, candidates are increasingly forced to undergo testing for knowledge of the program. The tasks check the knowledge of VLOOKUP, the ability to build a pivot table, knowledge of the basic functions, among which the SUMMES function is necessarily present. She is very important. In Excel, the SUMMS function significantly speeds up the process of calculating when it is necessary to calculate the sum according to one or more conditions.
Function Essence
In Excel, the SUMIF function is a mathematical function. Unlike a simple sum function, this allows you to calculate the sum only by elements that meet certain criteria.
The arguments for this function are:
- Range - all cells in which to search for a specified criterion
- Actually, the criterion is the condition by which summation occurs (it can be text, logical conditions, dates (both specific and date ranges). The use of certain types of criteria has its own characteristics that you need to know to use them in your work.
- The summation range is the cells whose values need to be added up as a result.
An example of applying the SUMMES function with one condition
For the SUMMES function in Excel, the simplest example is selective summation with one condition. For analysis, a table is provided (in the figure below), which contains information on how much, to which customer, in which region the managers of the company sold orders.
The data in the table is filled daily. At the end of the month, to calculate the bonus, A. K. Shcherbakov needs to find out what is the total amount of orders realized by this employee. In such cases, Excel uses the SUMMES function. To calculate, you need to perform the following actions:
- Put the cursor in the cell where you want to display the result
- On the tape, select "Formulas" - "Mathematical" - SUMMES. The Function Arguments window opens.
On the field of a specific argument, the necessary data is selected. In this case, the range should include surnames, so a column with the names of employees is highlighted. The criterion is the text. Keep in mind that Excel recognizes text if it is enclosed in quotation marks. The summation range will be the order value column, because it is this indicator that needs to be obtained in the end. When all the fields are filled in, pressing the OK key displays the result of the calculation.
Over the past month, manager A. Scherbakov, sold products for 54,302 rubles (the “Value” field in the dialog shows the result even before pressing OK). The above example shows that in Excel the SUMMES function allows you to quickly summarize the required indicators.
There are cases when the summation must be carried out not according to one, but according to several conditions. Starting from 2007, summarizing with several conditions can be done using the SUMMESlim function, which functionally extends the SUMMS function in Excel. Prior to the 2007 Excel version, in addition to using functions, you had to use other Excel tools, such as a filter.
SUMMES function in Excel: an example with several conditions
In the example considered above, it is necessary to find the sum of the orders realized by the manager I. Vorobyov to the customer IP A. Vaskov. Thus, there are two selection criteria: the employee and the customer. As in the previous example, we put the cursor in the cell where the result will be. "Formulas" - "Mathematical" - SUMMESlim. The Arguments window appears. It is filled somewhat differently from the previous example.
Here the summation range is the first, it is necessary to highlight the column with the cost of orders. Next are the ranges of conditions, you need to select the columns that contain them. The conditions themselves are written as text, similarly to the previous example.
SUMMES with a logical criterion (more, less, equal)
Often there is a need to use not a specific value as a criterion, but a range of values larger, smaller, or equal to a reference point.
In such cases, when a specified condition is specified in the criteria, it is enclosed in double quotation marks.
The figure shows how to calculate the sum of all orders worth more than 10,000 rudders.
Similarly, the function is used under other conditions, only the signs in the criteria area change:
- <- less;
- = - equal (can be omitted);
- <> - not equal;
- > = - greater than or equal to;
- <= - less than or equal to.
In Excel, the SUMES function with a criterion in the form of a logical expression is used quite often.
SUMMES with text criteria
Earlier, the use of text as a criterion in the SUMMES function was already considered. Of the features of the application, it should be noted that the text match may not be exact. To find the sum of orders in Moscow and the Moscow Region, the value "* mosk *" is entered in the criterion field. Asterisks suggest a partial match by criterion.
When working with textual criteria, there is no need to strictly follow the case.
SUMMES function with date as criterion
An example with dates for the SUMMES function in Excel can be represented by adding a column with sales dates in the original table. Let the task be the summation of all orders implemented on 10/15/2017 and later.
Comparison marks may change; instead of a specific date, the today function may be used. To sum values that refer to periods between two dates, the SUMMES function is used.
Of course, without Excel, the rhythm of work in many industries that is set today is impossible. Improvement in this program is the key to successful career growth for many specialists. And the SUMMES function is not the last assistant in all professional endeavors.