Google Sheets Formulas Cheat Sheet
The syntax for 59 of the most useful Google Sheets functions on one page. Arguments in [brackets] are optional. Click any function for a full guide with examples and practice exercises.
Math & Counting functions
| Function | Syntax | What it does | |
|---|---|---|---|
| SUM | =SUM(value1, [value2, ...]) | Adds up numbers and ranges. | Guide & practice → |
| AVERAGE | =AVERAGE(value1, [value2, ...]) | Returns the mean of the numbers, ignoring blanks and text. | Guide & practice → |
| MIN | =MIN(value1, [value2, ...]) | Returns the smallest number. | Guide & practice → |
| MAX | =MAX(value1, [value2, ...]) | Returns the largest number. | Guide & practice → |
| MEDIAN | =MEDIAN(value1, [value2, ...]) | Returns the middle number. | Guide & practice → |
| PRODUCT | =PRODUCT(factor1, [factor2, ...]) | Multiplies numbers together. | Guide & practice → |
| COUNT | =COUNT(value1, [value2, ...]) | Counts cells that contain numbers. | Guide & practice → |
| COUNTA | =COUNTA(value1, [value2, ...]) | Counts cells that are not empty. | Guide & practice → |
| COUNTBLANK | =COUNTBLANK(range) | Counts empty cells in a range. | Guide & practice → |
| LARGE | =LARGE(data, n) | Returns the nth largest number. | Guide & practice → |
| SMALL | =SMALL(data, n) | Returns the nth smallest number. | Guide & practice → |
| ROUND | =ROUND(value, [places]) | Rounds to a number of decimal places (half away from zero). | Guide & practice → |
| ROUNDUP | =ROUNDUP(value, [places]) | Rounds away from zero. | Guide & practice → |
| ROUNDDOWN | =ROUNDDOWN(value, [places]) | Rounds toward zero. | Guide & practice → |
| INT | =INT(value) | Rounds down to the nearest whole number. | Guide & practice → |
| ABS | =ABS(value) | Returns the absolute value. | Guide & practice → |
| MOD | =MOD(dividend, divisor) | Returns the remainder after division. | Guide & practice → |
| POWER | =POWER(base, exponent) | Raises a number to a power. | Guide & practice → |
| SQRT | =SQRT(value) | Returns the square root. | Guide & practice → |
Conditional functions
| Function | Syntax | What it does | |
|---|---|---|---|
| COUNTIF | =COUNTIF(range, criterion) | Counts cells in a range that meet one condition. | Guide & practice → |
| COUNTIFS | =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) | Counts rows that meet every condition. | Guide & practice → |
| SUMIF | =SUMIF(range, criterion, [sum_range]) | Adds values that meet one condition. | Guide & practice → |
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) | Adds values that meet every condition. | Guide & practice → |
| AVERAGEIF | =AVERAGEIF(criteria_range, criterion, [average_range]) | Averages values that meet one condition. | Guide & practice → |
| AVERAGEIFS | =AVERAGEIFS(average_range, criteria_range1, criterion1, [...]) | Averages values that meet every condition. | Guide & practice → |
Logical functions
| Function | Syntax | What it does | |
|---|---|---|---|
| IF | =IF(logical_expression, value_if_true, [value_if_false]) | Returns one value if a condition is TRUE and another if it is FALSE. | Guide & practice → |
| IFS | =IFS(condition1, value1, [condition2, value2, ...]) | Returns the value for the first TRUE condition. | Guide & practice → |
| AND | =AND(logical1, [logical2, ...]) | TRUE only if every condition is TRUE. | Guide & practice → |
| OR | =OR(logical1, [logical2, ...]) | TRUE if any condition is TRUE. | Guide & practice → |
| XOR | =XOR(logical1, [logical2, ...]) | TRUE if an odd number of conditions are TRUE. | Guide & practice → |
| NOT | =NOT(logical_expression) | Flips TRUE to FALSE and vice versa. | Guide & practice → |
| IFERROR | =IFERROR(value, [value_if_error]) | Returns a fallback when a formula produces an error. | Guide & practice → |
| IFNA | =IFNA(value, value_if_na) | Returns a fallback only for #N/A errors. | Guide & practice → |
| ISBLANK | =ISBLANK(value) | TRUE if the cell is empty. | Guide & practice → |
| ISNUMBER | =ISNUMBER(value) | TRUE if the value is a number. | Guide & practice → |
| ISTEXT | =ISTEXT(value) | TRUE if the value is text. | Guide & practice → |
| ISERROR | =ISERROR(value) | TRUE if the value is any error. | Guide & practice → |
Lookup functions
| Function | Syntax | What it does | |
|---|---|---|---|
| VLOOKUP | =VLOOKUP(search_key, range, index, [is_sorted]) | Searches the first column of a range and returns a value from the same row. | Guide & practice → |
| HLOOKUP | =HLOOKUP(search_key, range, index, [is_sorted]) | Searches the first row of a range and returns a value from the same column. | Guide & practice → |
| XLOOKUP | =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]) | Finds a value in one range and returns the matching item from another. | Guide & practice → |
| INDEX | =INDEX(reference, [row], [column]) | Returns the cell at a given row and column of a range. | Guide & practice → |
| MATCH | =MATCH(search_key, range, [search_type]) | Returns the position of a value in a row or column. | Guide & practice → |
Text functions
| Function | Syntax | What it does | |
|---|---|---|---|
| LEN | =LEN(text) | Counts the characters in text. | Guide & practice → |
| LEFT | =LEFT(string, [number_of_characters]) | Returns characters from the start of text. | Guide & practice → |
| RIGHT | =RIGHT(string, [number_of_characters]) | Returns characters from the end of text. | Guide & practice → |
| MID | =MID(string, starting_at, extract_length) | Returns characters from the middle of text. | Guide & practice → |
| UPPER | =UPPER(text) | Converts text to UPPERCASE. | Guide & practice → |
| LOWER | =LOWER(text) | Converts text to lowercase. | Guide & practice → |
| PROPER | =PROPER(text_to_capitalize) | Capitalizes The First Letter Of Each Word. | Guide & practice → |
| TRIM | =TRIM(text) | Removes extra spaces. | Guide & practice → |
| CONCATENATE | =CONCATENATE(string1, [string2, ...]) | Joins text together. | Guide & practice → |
| CONCAT | =CONCAT(value1, value2) | Joins exactly two values. | Guide & practice → |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...]) | Joins text with a separator between each item. | Guide & practice → |
| SUBSTITUTE | =SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) | Replaces existing text with new text. | Guide & practice → |
| FIND | =FIND(search_for, text_to_search, [starting_at]) | Position of text inside other text (case-sensitive). | Guide & practice → |
| SEARCH | =SEARCH(search_for, text_to_search, [starting_at]) | Position of text inside other text (not case-sensitive). | Guide & practice → |
| REPT | =REPT(text_to_repeat, number_of_repetitions) | Repeats text a number of times. | Guide & practice → |
| EXACT | =EXACT(string1, string2) | TRUE if two strings are identical, including case. | Guide & practice → |
| VALUE | =VALUE(text) | Converts text that looks like a number into a number. | Guide & practice → |