Contents
Formulation of the problem
We have a sales table, for example, of the following form:
Task: sum up all the orders that the manager Grigoriev implemented for the Kopeyka store.
Method 1. SUMIF function when one condition
If our task had only one condition (all Petrov’s orders or all orders for “Kopeyka”, for example), then the task would be solved quite easily using the built-in Excel function SUMMESLI (SUMIF) from category Mathematical (Math&Trig). Select an empty cell for the result, press the button fx in the formula bar, find the function SUMMESLI in the list:
Click OK and enter its arguments:
- Range – these are the cells that we check for execution Criterion. In our case, this is a range with the names of sales managers.
- Criterion is what we are looking for in the previous specified range. The characters * (asterisk) and ? (question mark) as wildcards or wildcards. An asterisk substitutes for any number of any characters, a question mark – any one character. So, for example, to find all sales from managers with a five-letter last name, you can use the criterion ?????. And to find all sales managers whose last name begins with the letter “P” and ends with “V” – the criterion P*V. Uppercase and lowercase letters are not distinguished.
- sum_range – these are the cells whose values we want to add, i.e. in our case, the cost of orders.
Method 2. The SUMIFS function when there are many conditions
If there are more than one conditions (for example, you need to find the sum of all Grigoriev’s orders for “Kopeyka”), then the function SUMMESLI (SUMIF) will not help, because does not know how to check more than one criterion. Therefore, starting with the version of Excel 2007, the function was added to the set of functions SUMMESLIMN (SUMIFS) – in it, the number of verification conditions has been increased up to 127! The function is in the same category Mathematical and works in a similar way, but with more arguments:
Using the scroll bar in the right part of the window, you can also set the third pair (Range_condition3—Condition 3), and the fourth, etc. – if necessary.
If you still have an old version of Excel 2003, but you need to solve a problem with several conditions, then you will have to pervert – see the following methods.
Method 3. Indicator column
Let’s add one more column to our table, which will serve as a kind of indicator: if the order was in “Kopeyka” and from Grigoriev, then the cell of this column will have the value 1, otherwise it will be 0. The formula that must be entered in this column is very simple:
=(A2=”Kopeck”)*(B2=”Grigoriev”)
The logical equalities in parentheses are TRUE or FALSE, which is equivalent to 1 and 0 in Excel. So, since we’re multiplying these expressions, we’ll end up with a unit only if both conditions are met. Now it remains to multiply the cost of sales by the values of the resulting column and sum up the selection in the green cell:
Method 4: Magic Array Formula
If you have not come across such a wonderful Excel feature as array formulas before, then I advise you to read a lot of good things about them first here. Well, in our case, the problem is solved by one formula:
=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)
After entering this formula, press not Enteras usual, but Ctrl + Shift + Enter – then Excel will perceive it as an array formula and itself will add curly braces. You don’t need to enter brackets from the keyboard. It is easy to see that this method (like the previous one) is easily scaled by three, four, etc. conditions without any restrictions.
Method 4: BDSUMM database function
In category Database (Database) function can be found BSSUMM (DSUM), which can also solve our problem. The nuance is that for this function to work, you need to create a special range of criteria on the sheet – cells containing selection conditions – and then specify this range of the function as an argument:
=БДСУММ(A1:D26;D1;F1:G2)
