As a rule, the effectiveness of large-scale, complex operations cannot be characterized using one indicator W; it must be assisted by other, additional W 1, W 2,..., W; Some of them it is desirable to do more, others - less. For example, when assessing the performance of an enterprise, a number of indicators have to be taken into account:

• full volume of products

  cost price, etc.

When analyzing a combat operation, in addition to the main indicator, the mathematical expectation of damage caused to the enemy, it is necessary to take into account a number of additional ones:

own losses

operation execution time,

ammunition consumption, etc.

This multiplicity of performance indicators, some of which are desirable to maximize and others to minimize, is characteristic of any complex operations research problem. In this case, the correct formulation is “achieving maximum effect at given costs” or “achieving a given effect at minimal costs.” In the general case, there is no solution that would turn one indicator W 1 into a maximum and at the same time another indicator W 2 into a maximum (or minimum); Moreover, such a solution does not exist for several indicators. However, quantitative analysis of effectiveness can also be useful in the case of several indicators, because it allows you to discard in advance clearly irrational solution options that are inferior to the best options in all respects.

Let's look at an example. Let us analyze the combat operation Q, assessed by two indicators:

W– probability of completing a combat mission;

S- cost of funds spent.

It is advisable to turn the first indicator to a maximum, and the second to a minimum.

Suppose there are 20 different solution options x 1, x 2,…, x 20. For each of them, the values ​​of both indicators W and S are known (see Fig. 1.1).

The figure shows that some solution options can be immediately discarded. Which options should be preferred when assessing effectiveness by two indicators. Obviously those that lie simultaneously on both the right and lower borders of the region (in Fig. 1.1 - dotted line). That. There are four options left: X 16, X 17, X 19, X 20. Of these, X 16 is the most effective, but relatively expensive; X 20 is the cheapest, but not as effective. The decision maker’s job is to figure out at what price we can pay for a certain increase in efficiency or, conversely, what efficiency we are willing to sacrifice in order not to incur too large material losses.

S x

x

x x x

x x x

x x

x x x

x x

x x

x

x x

Due to the fact that a comprehensive assessment of an operation using several indicators at once is difficult, in practice several indicators are combined into one generalized indicator. Often a fraction is taken as such a criterion; in the numerator put those indicators W 1 ,…, W , which it is desirable to increase, and in the denominator - those that it is desirable to reduce:

U=
(4)

A general disadvantage of a criterion of type (4) is that a lack of effectiveness in one indicator can always be compensated for by another (for example, a low probability of completing a combat mission due to low ammunition consumption, etc.).

Often, composite criteria are assumed to be a “weighted sum” of individual performance indicators:

U=α
+α
+…+α
(5)

where α are positive or negative coefficients.

Positive ones are set for those indicators that it is desirable to maximize; negative – for those that it is desirable to minimize. The absolute values ​​of the coefficients correspond to the degree of importance of the indicators. A criterion of type (5) has the same drawback (the possibility of mutual compensation of heterogeneous indicators) and can lead to incorrect recommendations. However, in cases where α i are not chosen arbitrarily, but are selected so that the composite criterion is the best performed its function, it is possible to obtain results of limited value with its help.

In some cases, a problem with several indicators can be reduced to a problem with one indicator if one singles out one (main) efficiency indicator W 1 and strives to maximize it, and impose only some restrictions of the form on the remaining auxiliary indicators W 2, W 3,...

W
; …W
; W
; ...W

These restrictions will then be included in the complex of specified conditions a 1 , a 2 ,… .

With this formulation of the problem, all performance indicators, except one, the main one, are transferred to the category given operation conditions. Solution options that do not fit within the given boundaries are immediately discarded. The recommendations obtained will obviously depend on how the limits for the supporting indicators are chosen. To determine how much this influences the final recommendations for choosing a solution, vary the restrictions within reasonable limits.

There is another possible way to build a compromise solution, which can be called “ method of successive concessions" Let the performance indicators be arranged in order of decreasing importance: first the main W 1, then other auxiliary ones: W 2, W 3,…. For simplicity, we will assume that each of them needs to be turned to a maximum (if this is not the case, it is enough to change the sign of the exponent). The procedure for constructing a compromise solution is as follows. First, a solution is sought that maximizes the main efficiency indicator W 1 . Then, based on practical considerations and the accuracy with which the initial data are known (often small), a certain “concession” ΔW 1 is assigned, which we agree to allow in order to maximize the second indicator W 2 . We impose a limitation on the indicator W 1 so that it is no less than W 1 * - ∆W 1 (W 1 * is the maximum possible value of W 1), and with this limitation we look for a solution that turns W 2 to a maximum.

Next, a “concession” is again assigned in the W 2 indicator, at the cost of which W 3 can be maximized, etc. This method is good because it is immediately clear at what cost “concession” in one indicator is gained in another. At the same time, the freedom to choose a solution, acquired at the cost of even minor concessions, can turn out to be significant, because In the region of the maximum, the efficiency of the solution usually changes very little.

One way or another, with any method of formalization, the task of quantitatively substantiating a decision based on several indicators remains not fully defined, and the final choice of solution is determined by the volitional act of the “commander.” The researcher’s job is to provide the “commander” with a sufficient amount of data, allowing him to comprehensively assess the advantages and disadvantages of each solution option and, based on them, make the final choice.

In the second part of our function tutorial VLOOKUP(VLOOKUP) in Excel we will look at several examples that will help you channel all your power VLOOKUP to solve the most ambitious Excel problems. The examples assume that you already have a basic knowledge of how this function works. If not, you might be interested in starting with the first part of this tutorial, which explains the syntax and basic usage VLOOKUP. Well, let's get started.

Search in Excel using multiple criteria

Function VLOOKUP Excel is a really powerful tool for searching for a specific value in a database. However, there is a significant limitation - its syntax allows you to search only for one value. What if you need to search using several conditions? You will find the solution below.

Example 1: Search using 2 different criteria

Let's say we have a list of orders and we want to find Quantity of goods(Qty.), based on two criteria – Client name(Customer) and The product's name(Product). The matter is complicated by the fact that each of the buyers ordered several types of goods, as can be seen from the table below:

Regular function VLOOKUP will not work in this scenario because it will return the first value found that matches the given lookup value. For example, if you want to know the quantity of an item Sweets, ordered by the buyer Jeremy Hill, write down the following formula:

VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

– this formula will return the result 15 , corresponding to the product Apples, since it is the first value that matches.

There is a simple workaround - create an additional column in which to combine all the necessary criteria. In our example these are the columns Client name(Customer) and The product's name(Product). Remember that the merged column must always be the leftmost column in the search range, since it is the left column that the function VLOOKUP looks when searching for a value.

So, you add an auxiliary column to the table and copy a formula like this across all its cells: =B2&C2. If you want the string to be more readable, you can separate the concatenated values ​​with a space: =B2&” “&C2. After this you can use the following formula:

VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=VLOOKUP("Jeremy Hill Sweets";$A$7:$D$18,4;FALSE)

VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

Where is the cell B1 contains the concatenated value of the argument lookup_value(search_value), and 4 – argument col_index_num(column_number), i.e. The number of the column containing the data to be retrieved.

Example 2: VLOOKUP based on two criteria with the table being viewed on another sheet

If you need to update the Main table by adding data from a second table (Lookup table), which is located on another sheet or in another Excel workbook, then you can collect the required value directly in the formula that you insert into the main table.

As in the previous example, you will need an auxiliary column in the Lookup table with the combined values. This column must be the leftmost column in the range specified for the search.

So, the formula with VLOOKUP could be like this:

VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=VLOOKUP(B2&" "Orders!$A&$2:$D$2;4;FALSE)

Here, columns B and C contain customer names and product names respectively, and link Orders!$A&$2:$D$2 defines a table to look up in another worksheet.

To make the formula more readable, you can give the range you are viewing a name, which will make the formula look much simpler:

VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=VLOOKUP(B2&" "Orders;4;FALSE)

For the formula to work, the values ​​in the leftmost column of the table you are viewing must be combined in the same way as in the search criteria. In the figure above, we combined the values ​​​​and put a space between them, the same must be done in the first argument of the function (B2&” “&C2”).

Remember! Function VLOOKUP limited to 255 characters, it cannot search for a value that is more than 255 characters long. Keep this in mind and make sure that the length of the value you are looking for does not exceed this limit.

I agree that adding an auxiliary column is not the most elegant and not always acceptable solution. You can do the same thing without the helper column, but that would require a much more complex formula with a combination of functions INDEX(INDEX) and MATCH(MATCH).

We extract the 2nd, 3rd, etc. values ​​using VLOOKUP

You already know that VLOOKUP can return only one matching value, or rather the first one found. But what if this value is repeated several times in the array you are viewing, and you want to extract the 2nd or 3rd of them? What if all the values? The problem seems complicated, but there is a solution!

Suppose one column of the table contains the names of customers (Customer Name), and the other contains the products (Product) that they purchased. Let's try to find the 2nd, 3rd and 4th products purchased by a given customer.

The simplest way is to add an auxiliary column before the column Customer Name and fill it with customer names with repetition number of each name, e.g. John Doe1, John Doe2 etc. Let's do the trick with numbering using the function COUNTIF(COUNTIF), given that the customer names are in column B:

B2&COUNTIF($B$2:B2,B2)
=B2&COUNTIF($B$2:B2,B2)

After this you can use the normal function VLOOKUP to find the order you need. For example:

• We find 2nd Dan Brown:

  VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)
  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

• We find 3rd goods ordered by the buyer Dan Brown:

  VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)
  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

If you are looking only 2nd repetition, you can do this without the helper column by creating a more complex formula:

IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,FALSE),"")
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4,0)+2)&":$C16"),2,TRUE)"")

In this formula:

• $F$2– a cell containing the buyer’s name (it is unchanged, please note – the link is absolute);
• $B$– column Customer Name;
• Table4– Your table (there may also be a regular range at this place);
• $C16– the end cell of your table or range.

This formula finds only the second matching value. If you need to extract the remaining repetitions, use the previous solution.

If you need a list of all matches, use the function VLOOKUP There is no help here, since it returns only one value at a time - that's it. But Excel has a function INDEX(INDEX), which can easily cope with this task. You will find out what such a formula will look like in the following example.

Retrieving all repetitions of the desired value

As mentioned above, VLOOKUP cannot extract all duplicate values ​​from the range being viewed. To do this, you will need a slightly more complex formula made up of several Excel functions, such as INDEX(INDEX), SMALL(SMALL) and ROW(LINE)

For example, the formula below finds all occurrences of a value from cell F2 in the range B2:B16 and returns the result from the same rows in column C.

(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"") )
(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;"");ROW()-3));"") )

Enter this array formula into multiple adjacent cells, e.g. F4:F8, as shown in the figure below. The number of cells must be equal to or greater than the maximum possible number of repetitions of the search value. Don't forget to click Ctrl+Shift+Enter to enter the array formula correctly.

If you're interested in understanding how it works, let's dive a little deeper into the details of the formula:

Part 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")
IF($F$2=B2:B16;ROW(C2:C16)-1;"")

$F$2=B2:B16– compare the value in cell F2 with each of the values ​​in the range B2:B16. If a match is found, then the expression ROW(C2:C16)-1 returns the number of the corresponding row (value -1 allows you to omit the header line). If there are no matches, the function IF(IF) returns an empty string.

The result of the function IF(IF) there will be a horizontal array like this: (1,"",3,"",5,"","",,"",,"",,"",,"",12,"","",,"" )

Part 2:

ROW()-3
ROW()-3

Here's the function ROW(ROW) acts as an additional counter. Since the formula is copied to cells F4:F9, we subtract the number 3 from the function result to get the value 1 in a cell F4(line 4, subtract 3) to get 2 in a cell F5(line 5, subtract 3) and so on.

Part 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

Function SMALL(SMALL) returns nth the smallest value in the data array. In our case, which position (from the smallest) to return is determined by the function ROW(LINE) (see Part 2). So, for the cell F4 function SMALL((array);1) returns 1st(smallest) element of the array, that is 1 . For cell F5 returns 2nd the smallest element of the array, that is 3 , and so on.

Part 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;""),ROW()-3))

Function INDEX(INDEX) simply returns the value of a specific cell in an array C2:C16. For cell F4 function INDEX($C$2:$C$16,1) will return Apples, For F5 function INDEX($C$2:$C$16,3) will return Sweets and so on.

Part 5:

IFERROR()
IFERROR()

Finally, we put the formula inside the function IFERROR(IFERROR), since you are unlikely to be happy with an error message #N/A(#N/A) if the number of cells into which the formula is copied is less than the number of duplicate values ​​in the viewed range.

Two-dimensional search by known row and column

Performing a two-dimensional search in Excel involves searching for a value using a known row and column number. In other words, you retrieve the cell value at the intersection of a particular row and column.

So let's go to our table and write down the formula with the function VLOOKUP, which will find information about the cost of lemons sold in March.

There are several ways to perform a two-dimensional search. Get to know the available options and choose the one that suits you best.

VLOOKUP and MATCH functions

You can use a bunch of functions VLOOKUP(VLOOKUP) and SEARCH(MATCH) to find the value at the intersection of fields The product's name(string) and Month(column) of the array in question:

VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

The formula above is a regular function VLOOKUP, which looks for an exact match of the value “Lemons” in cells A2 through A9. But since you don’t know which column the sales for March are in, you won’t be able to specify the column number for the third argument of the function VLOOKUP. Instead the function is used SEARCH to define this column.

MATCH("Mar",$A$1:$I$1,0)
MATCH("Mar",$A$1:$I$1,0)

Translated into human language, this formula means:

• We are looking for the symbols “Mar” – argument lookup_value(search_value);
• We look in cells from A1 to I1 for the argument lookup_array(view_array);
• Returning an exact match - argument match_type(match_type).

Using 0 in the third argument, you tell the function SEARCH look for the first value that exactly matches the search value. This is equivalent to the value FALSE(FALSE) for the fourth argument VLOOKUP.

This is how you can create a formula for a two-way search in Excel, which is also known as a two-dimensional search or two-way search.

SUMPRODUCT function

Function SUMPRODUCT(SUMPRODUCT) returns the sum of the products of the selected arrays:

SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX and MATCH functions

I'll be explaining these functions in great detail in the next article, so for now you can just copy this formula:

INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

Named Ranges and the Intersection Operator

If you're not crazy about all those complicated Excel formulas, you might like this visual and memorable way:

As you enter a name, Microsoft Excel will display a tooltip with a list of matching names, just like when you enter a formula.

1. Click Enter and check the result

In general, whichever of the above methods you choose, the result of a two-dimensional search will be the same:

We use several VLOOKUPS in one formula

It happens that the main table and the lookup table do not have a single column in common, and this prevents the use of the usual function VLOOKUP. However, there is another table that does not contain the information we are interested in, but has a common column with the main table and the lookup table.

Let's look at the following example. We have a Main table with a column SKU (new), where you need to add a column with the corresponding prices from another table. In addition, we have 2 lookup tables. The first one (Lookup table 1) contains updated numbers SKU (new) and product names, and the second (Lookup table 2) – product names and old numbers SKU (old).

To add prices from the second lookup table to the main table, you need to perform an action known as double VLOOKUP or nested VLOOKUP.

1. Write the function VLOOKUP, which finds the product name in the table Lookup table 1 using SKU, as the desired value:

   VLOOKUP(A2,New_SKU,2,FALSE)
   =VLOOKUP(A2,New_SKU,2,FALSE)

   Here New_SKU– named range $A:$B in the table Lookup table 1, A 2 – this is column B, which contains the names of the products (see the picture above)

2. Write down a formula to insert prices from a table Lookup table 2 based on well-known product names. To do this, insert the formula you created earlier as the search value for the new function VLOOKUP:

   VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)
   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   Here Price– named range $A:$C in the table Lookup table 2, A 3 is column C containing prices.

The figure below shows the result returned by the formula we created:

Dynamic substitution of data from different tables using VLOOKUP and INDIRECT

First, let’s clarify what we mean by the expression “Dynamic substitution of data from different tables” to make sure we understand each other correctly.

There are situations when there are several sheets with data of the same format, and it is necessary to extract the necessary information from a particular sheet depending on the value that is entered in a given cell. I think it's easier to explain this with an example.

Imagine having sales reports for several regions with the same products and in the same format. You need to find sales figures for a specific region:

If you have only two such reports, then you can use an outrageously simple formula with functions VLOOKUP And IF(IF) to select the desired report to search:

VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

• $D$2– this is a cell containing the name of the product. Please note that we are using absolute references here to avoid changing the lookup value when copying the formula to other cells.
• $D3– this is a cell with the name of the region. We use an absolute reference for the column and a relative reference for the row because we plan to copy the formula to other cells in the same column.
• FL_Sales And CA_Sales– names of tables (or named ranges) that contain the corresponding sales reports. You can of course use regular sheet names and cell range references, e.g. ‘FL Sheet’!$A$3:$B$10, but named ranges are much more convenient.

However, when there are many such tables, the function IF– this is not the best solution. Instead, you can use the function INDIRECT(INDIRECT) to return the desired search range.

As you probably know, the function INDIRECT is used to return a link given a text string, which is exactly what we need now. So, feel free to replace the expression with the function in the above formula IF to the link with the function INDIRECT. This is the combination VLOOKUP And INDIRECT works great in pairs:

VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

• $D$2– this is a cell with the name of the product, it is unchanged due to the absolute link.
• $D3– this is the cell containing the first part of the region name. In our example this is FL.
• _Sales– the common part of the name of all named ranges or tables. Combined with the value in cell D3, it forms the full name of the desired range. Below are some details for those who have no experience with the function INDIRECT.

How INDIRECT and VLOOKUP work

First, let me remind you of the function syntax INDIRECT(INDIRECT):

• A1, if the argument is equal TRUE(TRUE) or not specified;
• R1C1, If FALSE(LIE).

So let's get back to our sales reports. If you remember, each report is a separate table located on a separate sheet. For the formula to work correctly, you must give names to your tables (or ranges), and all names must have a common part. For example, like this: CA_Sales, FL_Sales, TX_Sales and so on. As you can see, all names contain “_Sales”.

Function INDIRECT connects the value in column D and the text string “_Sales”, thereby reporting VLOOKUP which table to look in. If the value “FL” is in cell D3, the formula will search the table FL_Sales, if “CA” is in the table CA_Sales and so on.

Result of the functions VLOOKUP And INDIRECT will be the following:

If the data is located in different Excel workbooks, then you need to add the workbook name before the named range, for example:

VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

If the function INDIRECT refers to another workbook, then that workbook must be open. If it is closed, the function will report an error #REF!(#LINK!).

KPI is a performance indicator that allows you to objectively assess the effectiveness of actions performed. This system is used to evaluate various indicators (the activities of the entire company, individual structures, specific specialists). It not only performs control functions, but also stimulates labor activity. Often, a remuneration system is built on the basis of KPIs. This is a method for forming the variable part of the salary.

KPI key performance indicators: examples in Excel

The stimulating factor in the KPI motivation system is monetary reward. It can be received by the employee who has completed the task assigned to him. The amount of the bonus/bonus depends on the performance of a particular employee in the reporting period. The amount of remuneration can be fixed or expressed as a percentage of the salary.

Each enterprise determines key performance indicators and the weight of each individually. The data depends on the company's objectives. For example:

1. The goal is to achieve a product sales plan of 500,000 rubles monthly. The key indicator is the sales plan. Measurement system: actual sales amount / planned sales amount.
2. The goal is to increase the amount of shipments in the period by 20%. The key indicator is the average shipment amount. Measurement system: actual average shipment / planned average shipment.
3. The goal is to increase the number of clients by 15% in a certain region. The key indicator is the number of clients in the enterprise database. Measurement system: actual number of clients / planned number of clients.

The enterprise also determines the spread of the coefficient (weights) independently. For example:

1. Fulfillment of the plan less than 80% is unacceptable.
2. Plan fulfillment 100% - coefficient 0.45.
3. Fulfillment of the plan 100-115% - coefficient 0.005 for every 5%.
4. No errors – coefficient 0.15.
5. There were no comments during the reporting period – coefficient 0.15.

This is only a possible option for determining motivational coefficients.

The key point in measuring KPI is the ratio of the actual indicator to the planned one. Almost always, an employee’s salary consists of a salary (fixed part) and a bonus (variable / variable part). The motivation coefficient influences the formation of the variable.

Let’s assume that the ratio of the constant and variable parts in the salary is 50 × 50. Key performance indicators and the weight of each of them:

Let us accept the following coefficient values ​​(the same for indicator 1 and indicator 2):

KPI table in Excel:

Explanations:

This is a sample KPI table in Excel. Each enterprise makes up its own (taking into account the characteristics of work and the bonus system).

KPI matrix and example in Excel

To evaluate employees against key performance indicators, a matrix, or agreement on goals, is drawn up. The general form looks like this:

1. Key indicators are the criteria by which the work of personnel is assessed. They are different for each position.
2. Weights are numbers in the range from 0 to 1, the total sum of which is 1. They reflect the priorities of each key indicator, taking into account the company’s objectives.
3. Base – acceptable minimum value of the indicator. Below the basic level – no result.
4. Norm – planned level. Something that an employee must do. Below - the employee failed to cope with his duties.
5. A goal is a value to strive for. An above-standard indicator that allows you to improve results.
6. Fact – actual results of work.
7. The KPI index shows the level of results in relation to the norm.

Formula for calculating kpi:

KPI index = ((Actual - Base) / (Norm - Base)) * 100%.

An example of filling out a matrix for an office manager:

The performance coefficient is the sum of the products of indices and weights. Employee performance ratings are clearly shown using conditional formatting.

Let's say you want to sum values ​​with more than one condition, such as the sum of sales of a product in a specific region. This is a good use of the SUMIFS function in a formula.

Notice this example where we have two conditions: sales amount required meat(from column C) to South areas (from column a).

Here is a formula that can be used for acomplish:

= SUMIFS (D2: D11; a2: A11; "South"; C2: C11; "meat")

The result is a value of 14,719.

Let's look at a more detailed representation of each part of the formula.

SUMIFS is an arithmetic formula. Calculates the numbers, which in this case are in column D. The first step is to specify the location of the numbers.

=SUMIFS(D2:D11,

In other words, you want the formula to sum the numbers in that column if they meet certain conditions. This cell range is the first argument in this formula, the first data element that the function uses as input.

Then you need to find the data that meets the two conditions and enter the first condition, specifying for the function the location of the data (a2: A11), and also that the condition is “South”. Note the commas between the individual arguments.

=SUMIFS(D2:D11,A2:A11,"South";

The quotation marks around the word "South" indicate that this is text data.

Finally, you enter the arguments for the second condition - the range of cells (C2:C11) that contain the word "Meat" as well as the word itself (enclosed in quotes) so that Excel can match them. Complete the formula by closing the closing parenthesis ) , and then press Enter. The result is again at 14,719.

=SUMIFS(D2:D11,A2:A11,"South",C2:C11,"Meat")

As you type the SUMIFS function in Excel, if you don't remember these arguments, Help is ready. After entering the formula = SUMIFS ( Autocomplete formulas will appear below the formula, and the list of arguments will be listed in the required order.

Take a look at the image of the formula autocomplete and argument list in our example sum_range is D2:D11, the column of numbers that need to be summed. criteria_range1- a2. A11 - data column containing condition1"Southern".

As you enter the formula, the remaining arguments (not shown here) will appear in Formula AutoComplete; range_conditions2 is the range C2:C11, which is the column of data that contains condition2- "Meat".

If you click SUMIFS When you autocomplete a formula, an article will open where you can get more information.

Try to practice

If you want to experiment with the SUMIFS function, here are some sample data and a formula that uses the function.

In this Excel for the web workbook, you can work with sample data and formulas right here. Change values ​​and formulas or add your own to see instant results change.

Copy all the cells from the table below and paste them into cell A1 of a new Excel sheet. You can adjust the width of the columns to make the formulas appear better.

VPR based on two or more criteria

Surely everyone who is familiar with the VLOOKUP function knows that it searches for specified values ​​exclusively in the left column of the specified table (you can read more about VLOOKUP in the article: How to find a value in another table or VLOOKUP strength). Also, many people know that VLOOKUP searches only on the basis of one value.

Did the article help? 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:alpha(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))