')) {alert('Спасибо за то что установили нашу кнопку! =)');} else {alert('Очень жаль! =(');}">

The function of the SUMMESLE, as well as the SUMMESLES by two criteria

  1. Search by tags
Tricks »11 June 2011 Dmitriy 243582 views

Imagine a table in which the names of departments (or accounts, or something else) are listed in rows in a row.

Sum the cells by criterion
It is necessary to calculate the total amount for each department. Many do it with a filter and writing with pens in the cells.
Although it can be done easily and simply with just one function - SUMMESLI .
SUMMESLES (SUMIF) —Sums up cells that satisfy a given condition (only one condition can be specified). This function can also be used if the table is divided into columns by periods (monthly, in each month, three columns - Income | Expense | Difference) and you need to calculate the total amount for all periods only by Income, Expense and Difference.

There are three arguments in total for the SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

  • Range (A1: A20000) - indicates the range with the criteria. Those. The column in which to search for the value indicated by the Criterion argument.
  • The criterion (A1) is the value (text or numeric, as well as the date) that must be found in the Range . May contain the wildcard characters "*" and "?". Those. specifying the Criterion "* mass *" to summarize the values ​​in which the word "mass" occurs. At the same time, the word “mass” can either occur anywhere in the text, or there can be only one this word in a cell. And specifying "mass *", all values ​​starting with "mass" will be summed up. "?" - replaces only one character, i.e. specifying "mas? a" you can sum up the lines with the value "mass" and the value "mask", etc.
    If the criterion is written in a cell and you still need to use wildcard characters, then you can make a link to this cell by adding the necessary one. Suppose you need to sum up the values ​​that contain the word "total." The word "total" is written in cell A1, while in column A there may be various spelling values ​​containing the word "total": "totals for June", "totals for July", "totals for March". The formula then should look like this:
    = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
    "*" & A1 & "*" - the & sign (ampersand) combines several values ​​into one. Those. the result will be "* result *".
    To better understand the principle of how formulas work, it is better to use the Calculate Formula tool: How to view the steps to calculate formulas
    All textual criteria and criteria with logical and mathematical signs must be enclosed in double quotes (= SUMMESLI (A1: A20000; "total"; B1: B20000)). If the criterion is a number, quotes are not required. If you want to find a question mark or an asterisk directly, you need to put a tilde (~) in front of it.
    About the tilde and its features can be found in this article: How to replace / remove / find asterisk?
  • Sum_Range (B1: B20000) (optional argument) - specifies the range of sums or numeric values ​​to be summed.

How it works: the function searches the Range for the value specified by the Criterion argument, and when a match is found, sums the data indicated by the Range_Amount argument. Those. if we have a department name in column A and an amount in column B, then specifying the Development Department as the criterion will result in the sum of all the values ​​of column B, opposite which the Development Department is found in column A. In fact, the SumArrangement may not be the same size as the Range argument and this will not cause an error of the function itself. However, when defining cells for summation, the top left cell of the Range_Amount argument will be used as the starting cell for summation, and then the cells corresponding in size and shape to the Range argument will be summed.

Some features
The last argument of the function (Sum_And_Band: B1: B20000) is optional. This means that it can not be specified. If you do not specify it, the function will add up the values ​​specified by the Range argument. What is it for. For example, you need to get the sum of only those numbers that are greater than zero. In column A of the amount. Then the function will look like this:
= SUMMERS (A1: A20000; "> 0")

What should be considered: the range_summing and the range should be equal in the number of lines. Otherwise, you can get the wrong result. Optimally, if it will look like in the formulas I have given: the range and range of summations start from one line and have the same number of lines: A1: A20000; B1: B20000

Summation over two or more criteria
But what to do when the criteria for summation 2 and more? Suppose you need to sum up only those amounts that belong to one department and only for a certain date. Happy owners of office versions 2007 and above can use the SUMMESLIMN function:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. The first argument specifies the range of cells containing the amounts that will be collected into one.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Specifies the range of cells in which you want to search for a match by criterion.
$ I $ 3, $ H8 - criterion. Here, as in SUMMESLI, the wildcard characters * and ? Are allowed . and they work the same way.

Specifics of specifying arguments: first, the criterion range is specified (they are numbered), then the value (criterion) is indicated directly in semicolon, which in this range must be found - $ A $ 2: $ A $ 50; $ I $ 3. And nothing else. You should not try to first specify all the ranges, and then the criteria for them - the function will either give an error, or it will not sum up what is necessary.

All conditions are compared according to the principle I. This means that if all the listed conditions are fulfilled. If at least one condition is not met, the function skips the line and does not add anything.
As for the SUMMERS, the summation and criteria ranges should be equal in the number of rows.

Because SUMMESLIMN appeared only in versions of Excel, starting from 2007, then how can unhappy users of earlier versions be in such cases? Very simple: use another function - SUMPRODUCT. I will not paint the arguments, because There are many of them and they are all arrays of values. This function multiplies the arrays indicated by the arguments. I will try to describe the general principle of using this function to summarize data on several conditions.
To solve the summation problem by several criteria, the function will look like this:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - date range. $ I $ 3 is the date of the criterion for which it is necessary to sum the data.
$ B $ 2: $ B $ 50 - the names of the departments. H5 - the name of the department, the data on which must be summed.
$ C $ 2: $ C $ 50 - range with amounts.

We analyze the logic, because to many, it will be completely unclear just by looking at this function. If only because in the help this application is not described. For greater readability, reduce the size of the ranges:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
So, the expression ($ A $ 2: $ A $ 5 = $ I $ 3) and ($ B $ 2: $ B $ 5 = H5) are logical and return arrays of logical FALSE and TRUE. TRUE if the cell of the range $ A $ 2: $ A $ 5 is equal to the value of the cell $ I $ 3 and the cell of the range $ B $ 2: $ B $ 5 is equal to the value of cell H5. Those. we have the following:
As you can see, in the first array there are two matches for the condition, and in the second one. Further, these two arrays are multiplied (the multiplication sign (*) is responsible for this). When multiplication occurs, the implicit conversion of arrays FALSE and TRUE to numeric constants 0 and 1, respectively ({0; 1; 1; 0} * {0; 0; 1; 0}) occurs. As you know, when multiplied by zero, we get zero. And the result is a single array:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Then the array {0; 0; 1; 0} is multiplied by an array of numbers in the range $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
And as a result, we get 30. What we needed - we get only the amount that meets the criterion. If there are more than one sum satisfying the criterion, then they will be summed up.

Advantage of SUMMYROIZV
If the arguments have the plus sign instead of the multiplication sign:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
then the conditions will be compared according to the OR principle: i.e. total sums will be summed up if at least one condition is fulfilled: either $ A $ 2: $ A $ 5 is equal to cell value $ I $ 3 or cell of range $ B $ 2: $ B $ 5 is equal to cell value H5.
This is the advantage of SUMMPRODUCT over SUMMESLIMN. SUMMESLIMN cannot sum up values ​​according to the OR principle, only according to the AND principle (all conditions must be fulfilled).

SUMPRODUCT cannot use wildcards * and?. It is possible to use more precisely, but they will be perceived not as special characters, but as an asterisk and a question mark. I think this is a significant disadvantage. And although this can be bypassed, I use other functions inside SUMPRODUCT — it would still be great if the function could somehow use wildcards.

In the example you will find a couple of examples of functions for a better understanding of what is written above.

Download an example

Amount by several criteria (41.5 KiB, 10,477 Downloads)

See also:
Summation of cells by fill color
Summation of cells by font color
Summation of cells by cell format
Calculate the amount of cells by fill color
Calculate the amount of cells by font color
How to sum up data from several sheets, including by condition

Article helped? Share the link with your friends! Video lessons

{"Bottom bar": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Search by tags

Go Access apple watch Multex Outlook Power Query and Power BI VBA work in the editor VBA code management Free add-ins date and time Charts and graphs Notes Data protection the Internet Pictures and objects Sheets and books Macros and VBA Add-ons Customization Print Search data Privacy Policy post office Programs Work with applications Work with files Application Development Summary Tables Lists Trainings and webinars Financial Formatting Formulas and functions Excel functions VBA Functions Cells and ranges MulTEx shares data analysis bugs and glitches in Excel links