excel more than 10 conditions how to judge and then calculate 5

There are two scenarios in which multiple conditions are met:

1) Multiple conditions are met at the same time;

2) One or more of the conditions are sufficient.

Let's use data as an example.

First, the and() function is used to illustrate that multiple conditions are met at the same time.

For example, if the text of column A is "a" and the data in column B is greater than 210, mark "y" in column C.

Enter the formula in C2:

if(and(a2="a",b2>210),"y","")

Knowledge point description:

The syntax of the and() function looks like this, and(condition1=criterion1, condition2=criterion2......Each condition and criterion is judged to be equal, and returns true if it is equal to it, and false if it is not. The and() function will return true only if all conditions and judgments are satisfied.

Then, the or() function is used to state that only one or more of the multiple conditions are met.

For example, if the text in column A is "a" or the data in column B is greater than 150, mark "y" in column C

Enter the formula in cell C2:

if(or(a2="a",b2>150),"y","")

Knowledge point description:

The syntax of the or() function goes like this: or(condition 1 = criterion 1, condition 2 = criterion 2......As with and, each condition and criterion judgment returns true or false, but as long as one of all judgments returns true, the or() function returns true.

The above method is judged in a single cell, or it can be written in the form of an array formula to complete the judgment of several auxiliary cells in a single cell at one time.

I don't know how many Vatican sends.

1. First of all, open Excel** in your computer, as shown in the figure below, and click "Formula Wizard".

2. Then click on "Conditional Judgment" in the Cover Selector item of the Potato Opening, as shown in the figure below.

3. Then, in the drop-down menu that opens, select "Judgment equals a certain value", as shown in the figure below.

4. In the pop-up box, the lower half of the box has the order of 1,2,3,4. If A3 cell (A) is equal to "B", the condition is "yes", otherwise "not", after filling in the data, choose to put the formula into a cell, here in order to facilitate the drop-down operation, put it in the b3 cell.

5. Finally, you can see the complete judgment function in cell b3, and the cursor pulls down the cell (you can also select the b3 to b7 area, press CTRL+D), and you can get the judgment results of each data, as shown in the figure below.

In Excel, you can use the if function to make a multi-condition judgment. The if function is a logical function that makes a judgment based on the condition, and if the condition is satisfied, it returns a value, otherwise it returns another value.

Here are the steps to make a multi-condition judgment in Excel:

Open Excel** and select the cell that needs to be judged by multiple conditions.

Enter the if function in the selected cell, such as "=if(and(a1>10,b1<5),"The conditions are met","The conditions are not met")”

In the if function, the AND function indicates that multiple conditions are met at the same time, and the OR function indicates that one of the conditions is satisfied. You can choose the appropriate logical bridge operator according to your needs.

Enter multiple conditions in the AND or function with a comma separating each condition.

If the condition is met, the if function returns a value, otherwise another value.

For example, if you want to tell if the value of a cell is greater than 10 and less than 5, you can use the following formula:

if(and(a1>10,a1<5),"The conditions are met","The conditions are not met")

If the value of A1 is greater than 10 and less than 5, the cell will display "Condition Satisfied", otherwise "Condition Not Met".

Here's how to fix it:

B1 input formula laughing limb potato: =if(a2<10,a2+15,if(a2<20,a2+20,if(a2<50,a2+60,a2)))).

2.Check B2

3.Pull down to complete the downward fill.

Microsoft Excel is an electronic software written by Microsoft for computers using Windows and Apple Macintosh operating systems. The intuitive interface, excellent calculation and charting tools, combined with successful marketing, make Excel the most popular software for processing personal computer data. With the release of Excel in 1993 as a component of Microsoft Office, Excel began to dominate spreadsheet software on all operating platforms.

1. Open Excel, take 90-99 points as an example, and enter =if() in the cell.

2. Click "() behind if, you can see the formula of the if function, and the "logical test" shows black and bold Kai Wang, indicating that the input at this time is a condition.

3. The first condition is "b3>90", and the second condition is "b3<99". To satisfy both conditions, you need to add a "() bracket to determine that all conditions are considered if functions (multiple conditions can be entered).

4. After the input condition is completed, enter the "value if true" input outside the parentheses, and enter the "value if true" input (the output value or match when the if condition is judged to be true).

The most awesome multi-condition formula.

sumproduct

Example of making pins:

sumproduct(--a1:a10="1 Jujube), b1:b10= 2 ) c1:c10= 3 ))).

The above formula is to find that column A is 1, column B is 2, column C is 3, how many towns are split?

The previous --, is to change the logical value to 1 or 0

<> enter or copy and paste the following formula in d2.

b2*1000+c2

Pull-down padding. Sort column d in descending order.

Enter the formula in cell d2:

rank(b2,b$1:b$27)+sumproduct(n((b$1:b$27=b2)*(c$1:c$27>c2)))

If B is the sales volume and C is the amount, D2=Rank(B2,B$2:B$1000) + Rank(C2,C$2:C$1000) 1000

e2=rank(d2,d$2:d$1000)

The two formula drop-downs can be auto-filled.

The previous data structure diagram with row and column labels illustrates the data structure and target requirements, otherwise there is no way to guess what you want to do!!

excelif determines multiple conditions: if function multi-condition judgment, sumif multi-condition sum, sumifs multi-condition sum, sumifs multi-condition sum, multi-condition sum with keywords, and multi-condition count.

1. Multi-condition judgment of if function

Requirements: If the department is production, the post is the main operation, there is a high temperature subsidy. Formula:

if(and(b2="production",c2="main operation"), have","none"); The and function determines that if both conditions are met, the if function returns "Yes", otherwise it is None.

2. sumif multi-condition summing

Requirements: Count the total amount of post subsidies for the two departments in the E2 and E3 cells of the shed potato width; Formula: sumproduct(sumif(b2:

b9,e2:e3,c2:c9））；The sumif function sums the condition using e2:

e3, get the job subsidy amount of the two departments respectively, and then use the sumproduct function to sum it.

3. Sumifs multi-condition summation

Requirements: The total amount of subsidies for the statistical department for production and the main operation of the post; Formula: sumifs(d2:

d9,b2:b9,f2,c2:c9,g2）；The sumifs function sums in the region d2:

d9, the sum of the hand answers is b2:b9=f2 and c2:c9=g2.

4. Multi-condition summation with keywords

Requirements: The total amount of subsidies for the statistical department including "production" and the main operation of the post; Formula: sumifs(d2:

d9,b2:b9,”＊f2&”＊c2:c9,g2）；The sumifs function supports the use of wildcards.

5. Multi-condition counting

Requirements: the number of people in the statistical department who are production, and the position is the main operation; Formula: countifs(b2:

b9,f2,c2:c9,g2）；The countifs function also supports the use of wildcards for chain lighting, which is the same as the sumifs function.

Enter the formula in the as2 cell:

if(w2+aa2=0,vlookup(e:e,'In 2021'!b:

e,4,0),if(ag2>=,vlookup(e:e,'In 2021'!b:

h,7,0),vlookup(e:e,'In 2021'!b:

e,4,0)+vlookup(e:e,'In 2021'!b:

f,5,0)))

The steps are as follows: 1. For example, column A of the classification is displayed, and the result is displayed in column B. Conditions: Greater than or equal to 90 is excellent, 90-60 is good, and below 60 is poor.

2. In B1, Chi Xiangju input=if(a1>=90,"Youma Bixiu",if(a1>=60,"Good","Poor"Press Enter.

3. Other units do not need to be entered by banquets, and the filling function is used to fill in the formula downward.

It is worth noting that if you judge the A1 value, if A1 is greater than or equal to 90, it will be excellent, otherwise if you judge that A1 is greater than or equal to 60, it will be good, otherwise it will be bad.