The article explains the principles of using functions for searching and summarizing, as well as their combinations. The formulas proposed for consideration work with one or more criteria and various types of data to summarize them in one table from several, summing up the general results based on the data and searching for information in tables with a large amount of data using the arguments specified in the condition. These feature combinations are:

1. They are used to create pivot tables in Excel that calculate the necessary data according to the specified condition, and then summarize the data associated with the results obtained.
2. They are also used to search (according to the specified criteria) for a value in the data array, followed by summing up the values ​​selected by common features from the second sheet.
3. In addition, combinations of the mentioned functions are used to search for a specific artist from the general list of accounts and add up all the values ​​based on the results of the search.

Tasks for the user may be different, but the essence is reduced:

• to search by specified conditions and summing the obtained values
• or to summarize a large amount of data into a single spreadsheet in Excel.

Search conditions can be completely different: from numbers and links to specific cells with the necessary data to logical functions and data obtained using other formulas.

Has Microsoft Excel created a function capable of doing the tasks mentioned above? Similar tasks are solved by compiling complex formulas with VLOOKUP() or VIEW() and SUM() or SUMIF(). The following are examples that show cases and ways to use these functions.

Please note that such expanded formulas imply that the user knows the general rules for using the VLOOKUP() function and its input. Otherwise, it is recommended to return to the initial lesson on the VLOOKUP() function, its basic use and composition.

Summing values ​​by given arguments using the VLOOKUP and SUM functions

When working with numbers, it is often necessary not only to extract numbers from a table, but also to add numbers from selected rows or columns. In such cases, a combination of the VLOOKUP() and SUM() functions is used.

The use of a combination of these two functions is considered here on the example of a table with a list of products and the results of their sales for a certain period of time, broken down by each month. So, you need to calculate the sales for all products separately for the entire period.

Datasheet – Monthly Sales:

This task is solved by using an array in the third argument (column_number) in the VLOOKUP() function.

=SUM(VLOOKUP(lookup value, interval lookup, {2,3,4}, FALSE)).

Based on the example above, in the array {2, 3, 4} of the considered argument, the VLOOKUP() functions are used to sum the numbers from columns 2,3 and 4.

To get the desired number from columns B to M, the combination of the VLOOKUP() and SUM() functions looks like this:

=SUM (VLOOKUP (B2, ‘Monthly sales’! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))

ATTENTION! Formulas containing arrays of data are closed through the key combination Ctrl + Shift + Enter, and not with the usual Enter. The formula will close in curly braces if manipulated in this way.

{=SUM (VLOOKUP(B2, ‘Monthly sales’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE)) }

If the function is closed with Enter, the calculation will not be done completely, but only by the first argument of the array.

TIP. The value you are looking for can be specified using a reference to the [@Product] column, as shown in Figure 2. Since the table in the example was created using the Insert → Table tool, it has become fully functional. In such a table, the changes made by default are duplicated on the selected column in the parent cell.

Based on this information, we can conclude that combining the VLOOKUP() and SUM() functions in work is quite simple. Although the proposed method is not the only right way, in particular, when working with tables with a large amount of data. Due to the fact that each argument makes a request to the VLOOKUP () function, the use of a large number of array formulas negatively affects the speed of the Excel workbook. Hence the conclusion: a large number of formulas in a workbook and arguments in an array can significantly slow down Excel.

Using a combination of MATCH() and INDEX() functions instead of VLOOKUP() and SUM() will get around this problem. The use of the proposed formulas will be studied in another tutorial.

Other kinds of tasks using the VLOOKUP function in Excel

Using the VLOOKUP() function, in addition to performing mathematical calculations from several columns using data extracted by arguments, you can similarly solve other mathematical problems together with other additional functions:

Attention! Since the above functions can contain arrays of data, they must end with the Ctrl+Shift+Enter key combination in order to correctly display the result.

If we consider the above formulas according to the sample table from the first example, the results will look like this:

VIEW and SUM – view the data array and summarize the matching data

If you need to search for values ​​among a large amount of data, and not a single number, the VLOOKUP () function will be ineffective, since it does not provide for working with an array of data. Then you should use the LOOKUP() function. It is similar to the VLOOKUP() function, but is suitable for working with arrays and individual arguments.

For example, there are two tables: the first one contains a list of customers, the list of products they bought and their quantity (main table), the second one contains the cost per unit of each product (lookup table). The task is to find out the total cost of purchases of a particular buyer.

Since there is a multiple selection from several data, the use of the VLOOKUP () function becomes impossible. Instead, we use a combination of the SUM() and VIEW() functions:

=SUM (LOOKUP ($C$2:$C, ‘Lookup table’!$A$2:$A$16, ‘Lookup table’! $B$2:$B$16)*$D$12:$D$10*($B $2:$B$10=$G$1))

Since the formula contains an array of data, it must be ended with the Ctrl+Shift+Enter key combination.

A detailed analysis of this formula with the analysis of the components will help to apply it to perform other tasks related to the selection of data and their summation from different tables.

The purpose of the SUM() formula is obvious and will be considered last. Let’s analyze 3 complex arguments that make up the formula:

1. =LOOKUP ($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)

In this case, the LOOKUP() function looks up the products from the list of column C in the main table and sets their unit price for the product from column B in the lookup table:

2. $D$2:$D$10

This example returns the quantity each customer bought from the list in column D of the main table. Multiplied by the price returned by the LOOKUP() function – calculates the total cost of each item purchased:

3. $B$2:$B$10=$G$1

Here, the list of buyers in column B is parsed with the given value to cell G1. In case of a match, it indicates the value “1”, if there is no match – “0”. This condition is only used to “cut off” other buyers. Since everyone knows that the result of any multiplication by 0 gives 0.

Due to the fact that this formula performs the above process for each individual argument in the search array, the last action is to sum the results of the above mathematical calculations through the SUM () function.

Attention! For the LOOKUP() formula to work correctly, you need to place the data in the lookup table in ascending order (from A to Z). If sorting is not available for the specified arguments, the TRANSPOSE function is used.

VLOOKUP() and SUMIF() find and sum the data that matches the specified condition.

The SUMIF() function is similar to the SUM() described above, used to sum numbers. The difference is that the SUMIF() function sums values ​​that meet user-defined criteria. The simplest function example: SUMIF = SUMIF(A2:A10, “>10”) adds numbers greater than 10 from cells A2 to A10.

Also, this function is suitable for more complex tasks. For example, there is a lookup table with a list of vendor names and their ID numbers. In the second table (main table) – the same ID and sales totals for all sellers separately. The task is to determine the amount of sales by the ID of a particular seller. However, there are two complicating factors:

•  In the second table, the same ID is repeated.
•  You cannot add names to the main table.

To draw up a formula, you first need to find all the amounts of sales for a particular person, and secondly, sum up the figures found.

Formula Syntax SUMIF(range, criteria, [summation range])

Range – the value of the parameter is clear from the name. Desired cells selected for comparison by the specified parameters.

• Criteria – an argument that defines values ​​from the range for performing calculations. The expression of this argument is allowed in numerical form, in the form of a function or a reference.
• Sum_range – This argument is required to be entered, but may be important. This condition means the range of cells where the result will be displayed. If you leave it as default, the results will be displayed in the cells of the range (1st parameter).

Given the definitions of the three parameters, the problem is to determine the total number of sales of each individual seller, given in cell F2 of the main table (see figure above).

1. Range – the seller is searched by ID, the ‘range’ parameter in the SUMIF() function is defined as column B in the first table. Thus, you can specify B:B, or, if the indicators are converted to table format, you can use the name of the column: Main_table [ID].

2. Criteria – sellers are presented as a list in the lookup table, you need to use the VLOOKUP () function to match the ID with the name of the seller. The desired seller is selected in cell F2 of the main table. The resulting formula looks like this: VLOOKUP($F$2, Lookup_table, 2, FALSE).

Of course, in this formula, you can specify the name of a specific seller, but it is more rational to use a cell reference, because with this approach, the formula becomes universal, it becomes possible to search for any employee specified by the link.

3. Sum range – since the sales amounts are in column C called “Sales”, this parameter is indicated by reference: Main_table [Sales].

Now, from the two components of SUMIF and VLOOKUP, a single formula is assembled:

=SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])

Using VLOOKUP without formulas in Excel

Information from tables can be viewed, compared, and combined in Excel without the aid of formulas. A special Merge Tables Wizard add-on has been created. It was created to save users time and as a simple replacement for the VLOOKUP() and LOOKUP() formulas, which can be very useful for both novice and advanced users.

Instead of writing lengthy formulas, the plugin enters main and lookup tables, defines a common column or columns, and specifies what data should be returned.

The program automatically compares two tables selected by the user according to the principle of the VLOOKUP formula, finds similarities in the two tables and updates the first table. In addition, there is an opportunity to select the columns that you want to compare, update information, highlight or filter the updated data.

It can be concluded that using the functions described above (or the plugin for replacing VLOOKUP), you can create a multifunctional table to combine information from several tables into one, edit it and summarize and calculate numerical and text data from the table, as well as search for them .