Popular Formula Tutorials
Mathematical Functions
Common formulas for adding, rounding and working with numbers.
SUM Function
Purpose: Adds numbers together.
Syntax: =SUM(A1:A10)
Example: =SUM(B2:B20)
SUMIF Function
Purpose: Sums values that meet a single condition.
Syntax: =SUMIF(range, criteria, [sum_range])
Example: =SUMIF(A2:A20,"North",B2:B20)
SUMIFS Function
Purpose: Sums values that meet multiple conditions.
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, ...)
Example: =SUMIFS(C2:C20,A2:A20,"North",B2:B20,">=100")
ROUND Function
Purpose: Rounds a number to a specified number of digits.
Syntax: =ROUND(number, num_digits)
Example: =ROUND(12.345,2)
ABS Function
Purpose: Returns the absolute value (removes the minus sign).
Syntax: =ABS(number)
Example: =ABS(-25)
Logical Functions
Formulas for decision-making and condition checks.
IF Function
Purpose: Returns a value based on a condition.
Syntax: =IF(A1>100,"Yes","No")
Example: =IF(B2>=60,"Pass","Fail")
IFS Function
Purpose: Tests multiple conditions and returns the first match.
Syntax: =IFS(test1, value1, test2, value2, ...)
Example: =IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"Fail")
AND Function
Purpose: Returns TRUE if all conditions are TRUE.
Syntax: =AND(logical1, logical2, ...)
Example: =AND(B2>=60,C2="Yes")
OR Function
Purpose: Returns TRUE if any condition is TRUE.
Syntax: =OR(logical1, logical2, ...)
Example: =OR(B2>=60,C2="Yes")
NOT Function
Purpose: Reverses TRUE/FALSE results.
Syntax: =NOT(logical)
Example: =NOT(B2>=60)
Lookup Functions
Find and return data from tables quickly.
VLOOKUP Function
Purpose: Searches data vertically and returns a matching value.
Syntax: =VLOOKUP(A2,F:G,2,FALSE)
Tip: Use XLOOKUP for a more flexible modern alternative.
XLOOKUP Function
Purpose: Modern replacement of VLOOKUP that can search left or right.
Syntax: =XLOOKUP(A2,F:F,G:G)
Example: =XLOOKUP(E2,A:A,B:B,"Not found")
INDEX + MATCH
Purpose: A powerful combo to look up values with flexibility.
Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example: =INDEX(B2:B20, MATCH(E2, A2:A20, 0))
Text Functions
Extract, join and work with text easily.
LEFT Function
Purpose: Returns characters from the left side of a text.
Syntax: =LEFT(text, [num_chars])
Example: =LEFT(A2,3)
RIGHT Function
Purpose: Returns characters from the right side of a text.
Syntax: =RIGHT(text, [num_chars])
Example: =RIGHT(A2,4)
MID Function
Purpose: Returns characters from the middle of a text.
Syntax: =MID(text, start_num, num_chars)
Example: =MID(A2,2,5)
LEN Function
Purpose: Returns the number of characters in a text.
Syntax: =LEN(text)
Example: =LEN(A2)
CONCAT Function
Purpose: Joins text strings together.
Syntax: =CONCAT(text1, [text2], ...)
Example: =CONCAT(A2," ",B2)
TEXTJOIN Function
Purpose: Joins text with a delimiter and can ignore blanks.
Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, ...)
Example: =TEXTJOIN(", ",TRUE,A2:A10)
Date & Time Functions
Work with dates, months and time-based calculations.
TODAY Function
Purpose: Returns today’s date.
Syntax: =TODAY()
Example: =TODAY()
NOW Function
Purpose: Returns the current date and time.
Syntax: =NOW()
Example: =NOW()
DATEDIF Function
Purpose: Calculates the difference between two dates.
Syntax: =DATEDIF(start_date, end_date, unit)
Example: =DATEDIF(A2,B2,"d")
EOMONTH Function
Purpose: Returns the last day of a month, before/after a given date.
Syntax: =EOMONTH(start_date, months)
Example: =EOMONTH(A2,1)
Statistical Functions
Count, average and find min/max values quickly.
COUNT Function
Purpose: Counts cells that contain numbers.
Syntax: =COUNT(value1, [value2], ...)
Example: =COUNT(A2:A100)
COUNTA Function
Purpose: Counts non-empty cells.
Syntax: =COUNTA(value1, [value2], ...)
Example: =COUNTA(A2:A100)
COUNTIF Function
Purpose: Counts cells that meet a condition.
Syntax: =COUNTIF(range, criteria)
Example: =COUNTIF(B2:B20,">=60")
AVERAGE Function
Purpose: Returns the average (mean) of numbers.
Syntax: =AVERAGE(number1, [number2], ...)
Example: =AVERAGE(C2:C20)
MAX Function
Purpose: Returns the largest number in a range.
Syntax: =MAX(number1, [number2], ...)
Example: =MAX(C2:C20)
MIN Function
Purpose: Returns the smallest number in a range.
Syntax: =MIN(number1, [number2], ...)
Example: =MIN(C2:C20)