www.hg82788.com 8 IF functions and 17 excel formulas you must learn at work

8 IF functions and 17 excel formulas you must learn at work

Public number: Excel elite training Source: Time: 2020-02-21 01:47:48

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.

Example :

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

Example :

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

Example :

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

Field smash

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

Example :

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

Example :

(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)

Spider pond