Work in many professional fields today is not complete without the use of the Excel program. This program, like any tool, becomes useful only in skilled hands. For successful activities in the field of economics, finance and many others, basic Excel knowledge is required. On the vast expanses of the Internet, representatives of professions related to the above areas formed a list of functions and capabilities that are urgently needed to solve professional problems. In addition to the ability to work with pivot tables, this list includes functions:
- SUMMES.
- IF A.
- SUMPRODUCT.
- VPR
- SUM.
Therefore, the SUMMES function in Excel is one of the most popular.
To successfully work with the function, you need to know some subtleties. With this article, even a newcomer to Excel can easily use the SUMMES function.
SUMMES function: function arguments dialog
If, when analyzing the data table in Excel, the task is to summarize indicators that meet certain conditions, then the time has come to apply the SUMMES function. The mouse cursor is placed in the cell where you want to display the result. Further, either the Excel function SUMMESLI is selected from the ribbon (Formula - mathematical - SUMMESLI), or is entered via the hot key =, which means the readiness to enter a formula or function in the cell. If after the = sign you begin to enter the first letters of the function name , Excel will display a few suitable ones, it remains only to select the desired one. Experienced users write SUMMESLI formula in Excel right in the line of formulas, entering arguments separated by semicolons. It is better for a beginner to use the fx icon, which is located to the left of the formula bar. The "Function Arguments" window appears. Proper filling of all windows is the key to successful work with the SUMMESLI function in Excel.
There are usually three fields:
- Range - a set of cells that the program will compare to meet a given condition. The cursor is placed in the argument field, and the desired cells are selected with the mouse.
- Criterion - a condition under which summation will be performed. There may be numbers, expressions, text, dates. Typically, the value of a criterion is entered in quotation marks.
- Summation range - cells with values that will be included in the total amount if the corresponding cell in the range meets the criterion. The cursor is placed in the argument field, and the desired cells are selected with the mouse.
2 fields can be filled in - a range and a criterion, this happens if the first one coincides with the summation range. The function arguments dialog box, the bottom line shows the resulting value. If everything is simple with ranges: you just need to identify the area of the analyzed cells, then to fill in the "Criteria" field you need to know some subtleties and features of the program.
Criteria for the SUMMES function in Excel
The criteria for the function can be:
- Logical expressions.
- Text.
- Exact dates.
- Time interval.
You can write values inside expressions from the keyboard, or you can refer to a cell with a value. The second method is more dynamic, it makes the result dependent on the cells that are referenced. For an example of using SUMMESLI in Excel, the following is a table of initial data, where the manager who sold a particular product in a certain quantity and at a known price on a specified date is indicated.
Boolean as a criterion
The most common use case for the SUMMS function in Excel is to use a boolean value (greater, less, equal) as a condition. For the above table, the amount of orders equal to 2000 rubles is described as follows.
As you can see from the example, the range and the summation range are the same, only the first line is filled. The condition is written in quotation marks (this is always true for this field!). The calculated value is immediately visible: 4000. We return to the initial table: indeed, there are two orders in the amount of 2000 rubles. Together they amount to 4000 rubles. Without using the dialog box, you can write in the formula bar: = SUMMES (E2: E15; "= 2000"), the result will be the same.
For an example, you can similarly set logical criteria using the expressions in the corresponding field:
- "> 2000" - the function will calculate the sum of all orders over 2000 rubles.
- "<2000" - the amount of orders less than 2000 rubles will be returned
- "> = 2000" - the sum of orders that are larger and equal to 2000 rubles will be returned.
- "<= 2000" - the amount of orders less than and equal to 2000 rubles.
Text as a criterion
SUMMES example in Excel by the text criterion: calculate how much orders were sold by manager I. Ivanov. This is how the arguments window looks in this case.
The function returned a value of 13,000 rubles. The manager Ivanov I.I. sold the goods for this amount
Sometimes there is a need for a condition to specify an inaccurate match in the text. For example, you need to find the amount received from orders of all mixers (there are two types of them in the table). For an inaccurate match, you need to put the * characters inside quotation marks.
The criterion field will contain the entry "* mixer *".
You can specify a condition with text that has a value of "not equal" by a combination of signs <>. For example, to calculate the amount of orders not from the sale of mixers, the function arguments window will look like this.
Sales of all goods except the mixer amounted to 34,200 rubles. When using text as a criterion, you can ignore the case of letters, matching conditions will be found in any case.
Date and date range as criteria
If the SUMMESLI function in Excel uses the specific date as the criterion for the condition, then the same criteria apply to the field design as in the case of numbers and text. The same signs =,>, <, <=,> =, <> correspond to the same values, only this applies to dates. For example, in the original table, you need to summarize orders for the first quarter of 2018. The sales date is selected as the range, the criterion, based on the assignment, looks like ""> = 03/31/2018 ", as a result, the following will turn out.
So, 19,600 rubles - the volume of sales according to the named nomenclature for the first quarter.
Link in the criterion field
Often, the final result from SUMMES calculations in Excel needs to be made dynamic, depending on changes in the cells included in the function arguments. If everything is clear with the first and third arguments - they are links initially, then in the "Criterion" field, in order to enter a link, you need to use an additional symbol - the ampersand (&) icon. Signs for a logical expression must, as before, be in quotation marks, and put & before the link.
In the source table, you need to find the amount for all orders that are larger than the order in cell E10. It looks like this.
Similarly, links can be inserted into any expression in the "Criterion" field in the arguments to the SUMMES function in Excel.
The examples discussed above imply the receipt of the amount under one condition. Fulfillment of several conditions by the SUMMESLI formula in Excel cannot be achieved. To implement this task, a more advanced function is used - SUMMESLIMN, introduced into the program starting from the 2007 version.