Excel function formula collection

A Complete Collection of Excel Functions and Formulas A Complete Collection of Excel Functions and Formulas Most Commonly Used in Work

1. Number processing

1. Take the absolute value

=ABS(number)

2. Rounding

=INT(number)

3. Rounding

=ROUND(number, decimal places)

2. Judgment formula

1. Display the error value generated by the formula as empty

Formula: C2

=IFERROR(A2/B2,"")

Note: If it is an error value, it will be displayed as empty, otherwise it will be displayed normally.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

2. IF multi-condition judgment return value

Formula: C2

=IF(AND(A2<500,B2="Unexpired"),"Replenishment","")

Note: If two conditions are true at the same time, use AND, and if either condition is true, use the OR function.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

3. Statistical formulas

1. Count the duplicate content of two tables

Formula:B2

=COUNTIF(Sheet15!A:A,A2)

Note: If the return value is greater than 0, it means it exists in another table, and 0 means it does not exist.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

2. Count the total number of unique people

Formula: C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

Instructions: Use COUNTIF to count the number of occurrences of each person, use division by 1 to convert the number of occurrences into the denominator, and then add them up.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

4. Summation formula

1. Sum every column

Formula: H3

=SUMIF($A$2:$G$2,H$2,A3:G3)

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

Note: If there is no rule in the title row, use the second formula

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

2. Single condition summation

Formula: F2

=SUMIF(A:A,E2,C:C)

Description: Basic usage of SUMIF function

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

3. Single condition fuzzy summation

Formula: See the picture below for details

Note: If you need to perform fuzzy sum, you need to master the use of wildcards. The asterisk represents any number of characters. For example, "*A*" means that there are any number of characters before and after a, including A.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

4. Multi-condition fuzzy summation

Formula: C11

=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

Note: Wildcard * can be used in sumifs

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

5. Summing multiple tables at the same position

Formula: b2

=SUM(Sheet1:Sheet19!B2)

Note: After deleting or adding a table in the middle of the table, the formula results will be automatically updated.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

6. Sum by date and product

Formula: F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

Note: SUMPRODUCT can complete multi-condition summation

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

5. Search and reference formulas

1. Single condition search formula

Formula 1: C11

=VLOOKUP(B11,B3:F7,4,FALSE)

Note: Search is what VLOOKUP is best at, basic usage

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

2. Bidirectional search formula

formula:

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

Description: Use the MATCH function to find the location and the INDEX function to get the value.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

3. Find the last record that meets the conditions.

Formula: See the picture below for details

Note: 0/(condition) can turn those that do not meet the conditions into error values, while lookup can ignore the error values.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

4. Search with multiple conditions

Formula: See the picture below for details

Note: The formula principle is the same as the previous formula

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

5. Search for the last non-null value in the specified area

Formula; see the figure below for details

Description: omitted

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

6. Get the corresponding value between the number areas

Formula: See the picture below for details

Formula description: Both the VLOOKUP and LOOKUP functions can take values based on intervals. It must be noted that the numbers in the sales column must be arranged in ascending order.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

6. String processing formula

1. Multi-cell string merging

Formula: c2

=PHONETIC(A2:A7)

Note: The Phonetic function can only merge character content, not numbers.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

2. Cut off the part except the last 3 digits

formula:

=LEFT(D1,LEN(D1)-3)

Note: LEN calculates the total length, LEFT cuts the total length from the left to -3

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

3. Intercept the part before -

Formula:B2

=Left(A1,FIND("-",A1)-1)

Description: Use the FIND function to find the location, and use LEFT to intercept.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

4. Formula to intercept any segment of the string

Formula:B1

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

Note: The formula is intercepted by forcibly inserting N null characters.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

5. String search

Formula: B2

=IF(COUNT(FIND("Henan",A2))=0,"No","Yes")

Explanation: FIND returns the position of the character if the search is successful, otherwise an error value is returned, and COUNT can count the number of numbers, which can be used to determine whether the search is successful.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

6. String search one-to-many

Formula: B2

=IF(COUNT(FIND({"Liaoning","Heilongjiang","Jilin"},A2))=0,"Other","Northeast")

Description: Set the first parameter of FIND to a constant array, and use the COUNT function to count the FIND search results.

A complete collection of Excel functions and formulas A complete collection of Excel functions and formulas most commonly used at work

7. Date calculation formula

1. Calculate the number of years, months and days between two dates

A1 is the start date (2011-12-1), and B1 is the end date (2013-6-10). calculate:

How many days apart?=datedif(A1,B1,"d") Result: 557

How many months apart? =datedif(A1,B1,"m") Result: 18

How many years apart? =datedif(A1,B1,"Y") Result: 1

Regardless of the number of months between years?=datedif(A1,B1,"Ym") Result: 6

Regardless of the number of days between years?=datedif(A1,B1,"YD") Result: 192

Regardless of the number of days between the year and the month?=datedif(A1,B1,"MD") Result: 9

Description of the third parameter of datedif function:

"Y" The number of whole years in the time period.

"M" The number of whole months in the time period.

"D" The number of days in the time period.

"MD" The difference in days. Ignore month and year in dates.

"YM" The difference in months. Ignore the day and year in the date.

"YD" The difference in days. Ignore the year in the date.

2. Number of working days excluding weekend days

Formula: C2

=NETWORKDAYS.INTL(IF(B2

Description: Returns the number of all working days between two dates, using parameters to indicate which days are weekends and how many days are weekends. Weekends and any days designated as holidays are not considered business days

Excel function formula collection

Related articles

Hot recommendations this week More