Millennium Tongzhou vitality north stream
I only love you
Today's IF function, including a series of functions that contain IF in excel, has a total of 8, blue for each function lists the most commonly used 2-3 formulas, I hope it can be useful to students.
函数 First, the IF function
Role : Judging according to the conditions and returning different values.
1. If the value of cell A1 is greater than 100, it displays "Done", otherwise it displays "Incomplete"
= IF (A1> 100, "Completed", "Incomplete")
2. Judging results
= IF (A1 <60, "Failed", IF (A1 <80, "Good", "Excellent"))
3. If the value of A1 is greater than 60 and B1 is not empty, display "Ended" otherwise display "Not Ended"
= IF (AND (A1> 60, B1 <> ""), "Ended", "Not Ended")
error函数 Second, IF error function
Role : Display the error value as the specified content
1. If A1 / B1 returns an error value, the display is empty
= Iferror (a1 / b1, "")
2. If Vlookup cannot find the value, it will display empty
= Iferror (vlookup (omitted), "")
函数 Third, the Count IF function
Role : Count the number according to conditions
1.Calculate how many people in the finance department are in column A
= Countif (a: a, "Finance Department")
2. Calculate how many company names in column A include "Beijing"
= Countif (a: a, "* Beijing *)
函数 Four, Sum IF function
： 根据条件求和 Role : Sum based on conditions
1.Sum of the sales amount of column C greater than 1000
= Sumif (c: c, "> 1000")
2. Count the sales of column A's products corresponding to column B of "Samsung"
= Sumif (a: a, "Samsung", b: b)
s函数 Five, Count IF s function
Role: Multi-Condition Counting
1. How many people are there in the personnel department of company 1 (column name A, department name B)
= COUNTIFS (A2: A11, "Company 1", B2: B11, "Personnel Department")
2. How many people have wages between 3000 and 4000 (D is listed as salary)
= COUNTIFS (D2: D11, "> 3000", D2: D11, "<4000")
s函数 Six, Sum IF s function
Role: Multi-conditional summation
（A列公司名称，B列部门名称。D列工资） 1. Salary of the Personnel Department of the company 1 (Company name in column A, Department name in column B. Salary in column D)
= SUMIFS (D2: D11, A2: A11, "Company 1", B2: B11, "Personnel Department")
2. Column A is "TV", and column B includes the sum of 34 and C
= Sumifs (C: C, A: A, "TV", b: b, "* 34 *")
函数 Seven, Average IF function
Role : Calculate averages based on conditions
1. Calculate the average unit price of the unit price of column C less than 10.
= AVERAGEIF (C: C, "<10")
2.Calculate the average unit price of product C (A is listed as the product name)
= AVERAGEIF (A: A, "C", B: B)
s函数 Eight, Average IF s function
Role : Calculate the average value under multiple conditions
（A列公司名，B列职称，C列工资金额） 1. Calculate the average salary of company A and manager level (A company name, B job title, and C salary amount)
= AVERAGEIFS (D2: D11, A2: A11, "Company A", B2: B11, "Manager")
2.Statistical average salary between 4000 ~ 8000
= AVERAGEIFS (D2: D11, D2: D11, "> 4000", D2: D11, "<8000")
下面二维码图片，点上面” 识别图中二维码 “然后再点关注，每天可以收到一篇兰色最新写的excel教程。 Long press on the QR code picture below, click on " Identify the QR code in the picture " and then pay attention. You can receive a blue excel tutorial written by you every day.
Excel Elite Training (excelpx-tete)