The excel function matches the corresponding value if both conditions are met at the same time

1. To sum the cells in the region that meet multiple conditions, you can use the sumifs function.

2. If necessary, to the area.

a1:a20

The values of the cells in B1:B20 are summed up: The corresponding values in B1:B20 are greater than zero.

Moreover. c1:c20

The corresponding value in is less than.

10, you can use the following formula: =sumifs(a1:a20,b1:b20,>0",c1:c20,<10")

3. The sumifs function can also use wildcards, i.e., question marks, in conditions.

and asterisks. *) The question mark matches any single character; The asterisk matches any sequence of characters. If you want to find the actual question mark or asterisk, type a tilde before the character.

Summary. First of all, the two matching files need a column of common data, as shown in the figure, the name in the first column is the common data, so that there is a basis to match other values. What they have in common is the name column, depending on personal needs, some of them need to match all the data, and some of the names are partial, but you have to know whether the common data is all the same, or only one of the divisions, it will only match what it corresponds to, and it can't be matched without a "primer".

Let's match the job numbers in Table 1 to Table 2.

Hello, I am helping you to inquire about the relevant information and will reply to you immediately.

First of all, the two files need a common column of data, as shown in the figure, the names in the first column are the common data, so that there is a basis to match other values. What they have in common is the name column, depending on personal needs, some of them need to match all the data, and some of the names, but you have to know whether the common balance data is the same for all the groups, or only one of the branches, it will only match what it corresponds to, and it can't be matched without an "introduction". Let's match the job numbers in Table 1 to Table 2.

Enter in the required **"=vlookup"

The column that looks for the value is the common data, the name column, select column b, and enter ","comma.

The data table is that you want to "move" the data to take a ** data, starting from the same column of the total defeat manuscript to the first column you need, the work number column, Cha Tongxiao and then enter, ","comma.

Then you will be prompted to select a column of name or a number, we select the number, and then enter, comma.

Next, it will prompt you to select an exact match and an approximate match, of course we choose to refine the shirt to match, and then enter, and we will get the work number corresponding to the name of the eggplant.

After dropping down the data, the unified data calculation formula gets all the required values.

Precautions. The cell format of the two common data columns needs to be the same, otherwise the correspondence cannot be recognized.

A lot of data matches are displayed incorrectly after the past, please adjust the cell format of the new column.

It's my pleasure to help you! Have a great day! If you think it is helpful to you, please give it a thumbs up! Thank you!

1. Open Excel** on the computer.

2. After opening Excel**, press Alt+F11 to bring up the VBA window, and then click Insert.

3. After clicking Insert, select the Insert module.

4. After inserting the module, copy**.

function abc(a as range, b as range, c as string)

dim t as string

If the area size of A and B is different, an "error" is displayed

if <>then abc = False": exit function

In area A is the circulation.

for i = 1 to

If the same value as c is found in a, the contents of b in the same row are extracted and stored in the variable t.

if , 1) =c then t = t & 1)

nextAssign the value of the variable to the custom function.

abc = t

end function

Copy-paste.

5. After pasting **, enter the formula =abc($a$1:$a$9,$b$1:$b$9,a1) in **.

6. After entering the formula, press enter to match multiple results.