- SUM+SUMIF
This function combination is used for conditional summation, as shown in the following figure. The input formula is:
=SUM (SUMIF (A2: A11, {“Li Xiaxia”; “Dai Mengmeng”}, D2: D11))
Moving Picture Cover
- SUM+COUNTIF
The combination of SUM function and COUNTIF function can be used to count the number of non duplicates. As shown in the figure below, enter the formula as:=SUM (1/COUNTIF (A2: A14, A2: A14)), and then press Ctrl+Shift+Enter to complete.
Moving Picture Cover
Explanation:
COUNTIF (A2: A14, A2: A14): Perform statistical judgments on the region;
1/COUNTIF (A2: A14, A2: A14): Remove duplicate values. If only one value appears, 1 divided by 1 is 1. If two values appear, 1 divided by 2 is 1/2. The sum of the two 1/2 values is also 1, which is equivalent to obtaining the number of non duplicate people.
- VLOOKUP+IF
When using VLOOKUP search, an error occurs when the search value is not in the first column. At this point, it is necessary to cooperate with the IF function to complete the normal search. As shown in the table below, the input formula is:=VLOOKUP (F2, IF ({1,0}, B2: B11, A2: A11), 2,0)
Moving Picture Cover
Explanation: Using IF ({I, 0}, region 1, region 2), reconstruct the data area to be searched for.
- VLOOKUP+MATCH
This function combination is very classic and is often used for cross queries, as shown in the table below. The input formula is:=VLOOKUP (A11, $A $1: $F $7, MATCH (B11, $A $1: $F $1,0), 0)
Moving Picture Cover
Explanation: First use MATCH (B11, $A $1: $F $1,0) to determine the column where the name is located, and then use the VLOOKUP function to find the reference.
- VLOOKUP+INDERECT
When VLOOKUP performs cross table or multi table queries, it is often necessary to use the INDERECT function in conjunction. As shown in the table below, the input formula is:
=IFERROR (VLOOKUP ($B2, INDERECT (C $1&”! B: C”), 2,0), “
Moving Picture Cover
Explanation: Here, the VLOOKUP and INDERECT functions are combined to dynamically reference the data search areas of columns B and C in each detail table. The formula also incorporates the IFERROR function for fault tolerance processing.
- INDEX+MATCH
This pair of combination functions is also commonly used for data query references. As shown in the table below, the input formula is:=INDEX (B2: B26, MATCH (D2, A2: A26,0))
Moving Picture Cover
Explanation: This formula first determines the position of the name in the specified area, and then combines the INDEX function to obtain the corresponding phone number.
- INDEX+SMALL+IF
This function combination is often referred to as “Find Three Swordsmen” and is used to obtain multiple values that meet the conditions. As shown in the table below, the input formula is:
=INDEX (B: B, SMALL (IF ($A $1: $A $11=$E $2, ROW ($A $1: $A $11), 4 ^ 8), ROW (A1)))&”
Moving Picture Cover
Explanation:
The SMALL function is used to locate the position of all E2 in column A (from small to large).
4 ^ 8 represents a relatively large number. In this IF formula, if the value of cell range A1: A11 is equal to E2, the row number of E2 in column A is displayed. If it is not equal to, a larger number is displayed.
After obtaining the line number using the SMALL function, combined with INDEX one to many search for the desired value.
The last ‘&’ is used to perform fault-tolerant processing.
- INDERECT+MATCH
The combination of INDERECT and MATCH functions can achieve reverse queries, which is very commonly used. As shown in the table below, the input formula is:
=INDERECT (“A”&MATCH (“Wang Wu”, C2: C8,0)+1)
Moving Picture Cover
Explanation: First, use the MATCH function to return the fourth row where Wang Wu is located in the search area, which is “A”&MATCH (“Wang Wu”, C2: C8,0)+1, and return the result as A5. Then, combine with the INDERECT reference function to return the cell reference.
- OFF SET+MATCH
Multi condition search references are quite suitable for using this pair of functions. As shown in the table below, the input formula is:=OFF SET (A1, MATCH (H2, A2: A11,0), MATCH (G2, B1: E1,))
Moving Picture Cover
Explanation: First, use the MATCH function to locate the positions of the specified month and product in the A2: A11 and B1: E1 data areas. Then, use them as the second and third parameters of the OFF SET function, and offset the corresponding number of rows and columns with A1 as the reference position.
- IF+AND/OR
The combination of IF and AND functions can be used to obtain values that satisfy multiple conditions simultaneously. As shown in the table below, the input formula is:=IF (AND (B2=”female”, C2>85), “excellent”, “”)
Moving Picture Cover
The combination of IF function and OR function can be used to obtain a value that satisfies any of the conditions. As shown in the table below, the input formula is:=IF (OR (C2>90, D2>90), “Excellent”, “”)
Moving Picture Cover
- Left/LIGHT+LEN+LENB
These two pairs of combination function formulas are generally used to extract specified content in mixed text. As shown in the table below, the input formula is:=Left (C2, LENB (C2) – LEN (C2))=LIGHT (C2,2 * LEN (C2) – LENB (C2))
Moving Picture Cover
Moving Picture Cover
- SUMPRODUCT+SUBSTITUTE
The SUMPRODUCT function can be combined with the SUBSTITUTE function to sum and statistically analyze data with units. As shown in the table below, the input formula is:=SUMPRODUCT (SUBSTITUTE (C2: C11, “Yuan”, “”) * 1)&”Yuan”
Moving Picture Cover
Explanation: SUBSTITUTE (D2: D10, “Yuan”, “”) will first replace all “Yuan” in column C with empty values, then multiply by 1 to convert the text into a numerical value, and then use the SUMPRODUCT function to sum it up.
Edited on January 13, 2023 at 13:38, IP Territory Fujian
Agree 661
35 comments
Sharing
Collection
Likes
Put it away
More answers
Touching fish
Touching fish
On the waves of the world
=LOOKUP (9E+307, — MID (A1, MIN (FIND ({0; 1; 2; 3; 4; 5; 6; 7; 8; 9}, A1&1234567890)))), ROW (INDERECT (“1:”&LEN (A1)))))
The consecutive digits that appear for the first time from the left in the output text.
For example:
Output:
Output:
This formula can save a lot of time when doing dirty work.
