Sumifs function multi-condition matching

Sumifs function multi-condition matching

SumIfs function multi-condition matching, SumIfs function is one of several summation functions in Excel, and the multi-condition of Sumifs function is represented by array. Share with you the multi-condition matching of sumifs function.

Sumifs function multi-condition matching 1 Method 1: Add auxiliary column method

Common Vlookup matching applications can only find one cell. For multiple conditions, it is enough to put multiple conditions into one cell.

Insert a column as an auxiliary column in the original table, and then enter =, using the text connector &; Connect different cells and merge them into one cell!

The query list is the same!

Finally, write Vlookup to achieve it!

Method 2: Vlookup function and array reconstruction first formula

In fact, with the idea of the first method, the second method is to insert a column of auxiliary columns into the virtual table with array function.

Formula: {= vlookup(G2 &；; H2，IF({ 1，0}，b 1:B9 & amp； C 1:C9，D 1:D9)，2)，}

The formula is enclosed in braces. What does this mean? It means that after entering the function, press Ctrl Shift Enter to end!

Why is shrimp so complicated? Because we use array functions, many formulas today end with three keys.

Explain the first parameter of Vlookup first.

G2 & ampH2 is the combination of two cells, and the result is Ishihara Congmei Maoming, which is similar to the auxiliary column just created!

The second parameter of Vlookup refers to a region, and here we use the IF function to build a region.

First, recall the usage of the IF function.

IF (judging conditions, what to return when true, what to return when false)

What does {1, 0} mean? In fact, it is generally understood that this is two columns. The numbers in the first column are all 1, and the numbers in the second column are all 0.

Translating into Excel means changing one column into two columns.

After the transformation

The first column is = if (1, b1:B9&; C 1:C9，D 1:D9)

The second column is: = if (0, b1:B9&; C 1:C9，D 1:D9)

So Excel reconstructed a new table for us. The first column of this table is the combination of name and city, and the second column is the score. The method of creating auxiliary columns is actually the same as the first method.

The only difference is that the first method is to create a new table manually, and the second method is to create a table virtually through the if plus array function.

Method 3: Vloo kup function and the second formula of array reconstruction

This method is similar to the second method, but the form is different when constructing the array auxiliary table.

Formula: {= vlookup (1, if ({ 1, 0}, (b1:B9 = G2) * (c1:C9 = H2), D 1: D9.

The auxiliary table of this method becomes that each column is equal to the condition, and then the two conditions are multiplied.

B 1:B9=G2 gets an array of True and False.

C 1:C9=H2 gets the same True and False arrays.

True equals 1 and false equals 0.

When multiple conditions are met at the same time, it becomes 1, otherwise it is 0.

If they are equal, the first column becomes 1, and if any of them are not equal, the final result is 0.

The second column is psychological score.

Then Vlookup looks up according to 1, and only two conditions are equal, the new auxiliary table is 1, otherwise it is 0.

Then there is only one return value, which is 6!

The essence of this case is to deeply understand how the array is reconstructed and what the reconstructed table looks like!

Method 4: uncle search implementation

Lookup and Vlookup are cousins. Although Lookup is not used as frequently as Vlookup, it can solve problems more skillfully in many occasions!

Formula: = lookup (1, 0/((B2: B9 = G2) * (C2: C9 = H2)), D2: D9)

There are no braces in this formula. Press Enter to complete the formula!

It is very important to explain the second parameter 0/(B2:B9=G2)*(C2:C9=H2).

When a column equals a cell, you get an array of True and False, and the multiplication of the two arrays is an array of 1 and 0.

Because the number 0 can't be the denominator, if it is the denominator, it will be an error!

(B2:B9=G2)*(C2:C9=H2) Return value: {0; 0; 0; 0; 0; 0; 1; 0}

0/(B2:B9=G2)*(C2:C9=H2) Return value: {#DIV/0! ; #DIV/0！ ; #DIV/0！ ; #DIV/0！ ; #DIV/0！ ; #DIV/0！ ; 0; #DIV/0！ }

Then the auxiliary table of Lookup's second parameter has only the penultimate significant number, so there is only a unique return value!

Remarks: The most difficult thing to understand in this case is why the first parameter is 1 and the numerator of the second parameter is 0! In fact, the first parameter can be any number, as long as it is greater than the molecule of the second parameter

Because the principle of lookup is to return the return value corresponding to the first parameter number in the auxiliary table!

Method 5: Match the index Dafa!

Matching and index matching can fully realize the application of Vlookup, and can also realize matching functions that Vlookup itself cannot achieve, such as reverse query.

Introduction to basic functions

=Match (search content, in which column, 0) returns the position of the first parameter in the second parameter.

=Index (column, returns the value of the column) returns the nth value in the column.

The two combinations are the application of Vlookup!

Formula: {= index (d2: d9, match(G2 &；; B2 H2:B9 & amp； C2:C9，0))}

Idea: First get the ranking of the searched content in the new column, and then return the value of the corresponding position of the scoring column!

The key is the application of matching function. The first parameter that matches is a combination of two conditions, and the second parameter should be followed by a column. In this case, I multiply two columns and realize that each column is linked together with a text connector at the same position, which is the same as creating an auxiliary column! With the above foreshadowing, I won't go into details!

Method 6: Sumifs implementation

Sumifs is Sumif's eldest brother, Sumif can only achieve single conditional statistical summation, and Sumifs can achieve N conditional statistical summation!

=Sumifs (column to be summed, column to be judged 1, judgment condition 1, column to be judged 2, judgment condition 2 ...)

Formula: = sumifs (D2: D9, B2: B9, G2, C2: C9, H2)

Methods the realization of Sumproduct function

Formula: = sumproduct ((B2: B9 = G2) * (C2: C9 = H2) * D2: D9)

Sumproduct is that sum of array product,

Method 8: Judge sum, array function

Formula: {= sum ((B2: B9 = G2) * (C2: C9 = H2) * D2: D9)}

Sumifs function multi-condition matching 2 method/step

1

Sumifs function is a function that sums several conditions.

2

Open the data table.

three

Enter requirements and conditions. If the conditions are large, it is very clear to enter it in advance in the form.

four

Enter the formula =sumifs(C2:C23,, the data range to be calculated.

five

Continue to enter the formula =sumifs(C2:C23, A2:A23, add condition 1 area.

six

Continue to input the formula =sumifs(C2:C23, A2:A23, F2, and add the condition 1.

seven

Continue to enter the formula = sumifs (C2: C23, A2: A23, F2, B2: B23, and add the condition 2 area.

eight

Continue to input the formula = sumifs (C2: C23, A2: A23, F2, B2: B23, G2) and add condition 2. If conditions permit, you can continue to join.

nine

After entering the car, get the result.

Multi-conditional matching of sumifs function 3 I. Excel Sumifs syntax

1, expression: sumifs (sum _ range, criteria _ range 1, criteria 1, [criteria _ range2, criteria 2], ...)

Chinese expression: SUMIFS (sum region, conditional region 1, conditional region 1, [conditional region 2, conditional 2], ...)

2. Description:

A in the expression, the first three parameters are required, and the parameters in square brackets ([]) are optional. The ellipsis (...) means to continue the construction [condition 3 area, condition 3], [condition 4 area, condition 4], ..., [condition area n, condition n].

B, you can use the wildcard "question mark (? ) and asterisk (*) ",the question mark matches any single character, and the asterisk matches any one or a string of characters; "If you want to find? And * ",you need to add an escape character in front of them ~ for example, looking for? , need to write ~? .

C. If a text condition is used in a condition, the condition containing logical or mathematical symbols must be enclosed in double quotation marks ("); For example, if you use the greater than sign, you should write it like this: "> 50" or ">" & amp50.

D, SumIfs only sums the values, ignoring the words. If the selected summation area is full text, it returns 0; If there are both text values and numeric values, only the numeric values are summed.

Second, the use of Excel Sumifs function example

(1) single condition

1, if you want to calculate the sum of all clothing sales in Guangzhou. Select cell H2, copy the formula = sumifs (F2: F 10, D2: D 10, "Guangzhou") to H2, and press enter to return the sum of all clothing sales in Guangzhou, as shown in figure 1:

Figure 1

2. Description of formula

In the formula, = SUMIFS (F2:F 10, D2:D 10, "Guangzhou"), F2:F 10 is the region for seeking peace, D2: D 10 is the conditional region, and "Guangzhou" is the condition.

(2) Multiple conditions

1, double condition

A, if you want to count the sum of all the clothing sales of "shirts" in Guangzhou. Copy the formula = SUMIFS (F2: F 10, D2: D 10, "Guangzhou", C2:C 10, "shirt") into the H2 cell, and press Enter to calculate the sum of all eligible sales, as shown in Figure 2.

B, formula description

The summation region of the formula = SUMIFS (F2: F 10, D2: D 10, "Guangzhou", C2:C 10, "shirt") is F2: f10; The conditional area 1 is D2:D 10, and the conditional area 1 is "Guangzhou"; Conditional area 2 is C2: C 10, and conditional area 2 is "shirt".

2. Combination of array condition and Sum function

A, if you want to count the sum of shirts sold in Guangzhou and Shenzhen. Select cell H2, copy the formula = SUM (SUMIFS (F2: F 10, D2: D 10, {"Guangzhou", "Shenzhen"}, C2:C 10, "shirt") to H2, and press enter to calculate the satisfactory shirt.

B, formula description

The formula = Sum (SumIfs (F2: F 10, D2: D 10, {"Guangzhou", "Shenzhen"}, C2:C 10, "shirt") consists of two functions, and the SumIfs function is used to count "Guangzhou" respectively. SumIfs condition 1 "{ "Guangzhou" and "Shenzhen"} "are arrays, and there are only two conditions in the array. If you want to add a condition, you can add it later.

Tip: If the Sum function is not used, only the "shirt" sales of "Guangzhou" are calculated.

3. Multiple array conditions

First, if you want to count the sum of clothing sales in Guangzhou and Hangzhou with sales prices of 86, 80 or 65 yuan. Formula = sum (sumifs (F2: f 10, D2: d 10, {"Guangzhou", "Hangzhou"}, E2:E 10, {86; 80; 65}) to H2 unit, as shown in Figure 4:

B, press the Enter key, and the sum of clothing sales that meet the requirements will be counted, as shown in Figure 5:

Note: Condition 2 {86; 80; Use semicolons (; ) 65}. ), if a comma (,) is used, only the first qualified sales volume will be returned; The result returns to 329, which is the first record of the sale of "Long Sleeve White Shirt", as shown in Figure 6:

(3) Use wildcards to combine conditions

1. If the product name is four words and the sales area contains the word "state", the price is greater than the sum of all the clothing sales in 60 yuan. The formula = sum (sumifs (F2: f 10, B2: b 10, "",D2: d 10, "* state *", E2:E 10, "& gt60".

2. Description of formula

Formula = sum (sumifs (F2: f10, B2: b 10, "",D2: d 10, "* state *", E2:E 10, "& gtsumifsin. The first group (B2:B 10, "") is looking for clothes with the word B2: b10; The second group (D2:D 10, "* state *") finds clothes with the word "state" from D2: d10; The third group (E2:E 10, "> 60") is the clothing whose "price" is higher than that of 60 yuan from E2: E 10. Finally, Sum the sales of three groups of clothing with SUM.