A listing, by category, of the functions in Google Sheets. We explain how they work and when you may want to use them.
Name | Purpose | Category | Inputs |
---|---|---|---|
ARRAYFORMULA | repeats non-array formulas | array_formula | |
AVERAGE | calculates the mean of a set of numbers | Statistical | numbers |
AVERAGEA | calculates the mean of a set of numbers, text, and boolean values | Statistical | values |
COUNT | counts the numeric values in a data set | Statistical | numbers |
COUNTA | counts the non-blank values in a data set | Statistical | values |
COUNTIF | counts cells that match a criterion | Math | values |
COUNTIFS | counts cells that meet multiple criteria | Math | values |
COUNTUNIQUE | counts the number of unique values | Math | values |
COUNTUNIQUEIFS | counts the number of unique values based on conditions | Math | values |
DATE | returns the inputs as a date | Time and Date | year, month, day |
DATEDIF | returns the number of days, months, or years between two days | Time and Date | start date, end date, unit |
DATEVALUE | returns a serial number from any valid date | Time and Date | date in text format |
DAY | returns a date’s day value as a number | Time and Date | date |
DAYS360 | returns the difference between two dates using the 360-day method | Time and Date | start date, end date, [method] |
DAYS | returns the number of days between two dates | Time and Date | end date, start date |
DB | returns one period of accelerated depreciation | Financial | cost, salvage value, life, period, [month] |
DDB | returns one period of accelerated depreciation | Financial | cost, salvage value, life, period, [factor] |
EDATE | returns a date a given number of months away from a specified date | Time and Date | start date, # of months |
EOMONTH | returns end date of a month x months away from a date | Time and Date | start date, # of months |
FILTER | returns filtered rows from a table | Filter | range,condition1,[condition2…] |
GOOGLEFINANCE | imports security data from Google Finance | ticker symbol, [attribute], [start_date], [end_date|num_days], [interval] | |
HOUR | returns a time’s hour value as a number | Time and Date | time |
IMAGE | inserts an image into a cell | URL, mode, [height], [width] | |
IMPORTRANGE | imports data from another spreadsheet | Web | spreadsheet URL, range string |
IMPORTXML | imports XML data from the web | Web | URL, XPath_query, [locale] |
INDEX | retrieves a specified value by row and column | Lookup | reference, [row], [column] |
ISOWEEKNUM | returns the ISO week number of a given date | Time and Date | date |
MINUTE | returns a time’s minute value as a number | Time and Date | time |
MONTH | returns a date’s month value as a number | Time and Date | date |
NETWORKDAYS | calculates the number of working days between two dates | Time and Date | start date, end date, [holidays] |
NETWORKDAYS.INTL | calculates the number of working days between two dates | Time and Date | start date, end date, [holidays], [weekends] |
NOW | returns the current date and time | Time and Date | none |
QUERY | uses SQL-like syntax to analyze data returned as a new table | data, query, [headers] | |
RAND | generates a random decimal | Math | none |
RANDBETWEEN | generates a random integer between two given numbers | Math | low, high |
RANDARRAY | generates an array of random decimals | Math | rows, columns |
SECOND | returns a time’s second value as a number | Time and Date | time |
SLN | returns one period of straight-line depreciation | Financial | cost, salvage value, life |
SORT | returns a sorted table | Filter | range,sort_column,is_ascending,[sort_column2, is_acending2,…] |
SORTN | returns n sorted lines from a table | Filter | range,[n],[display_ties_mode],[sort_column1, is_acending1],… |
SPARKLINE | inserts a miniature chart into a cell | data, [options] | |
SUBTOTAL | performs a variety of functions based on the function number | Math | function number, numbers |
SUM | adds numbers | Math | numbers |
SUMIF | adds numbers that meet a condition | Math | numbers, criterion, [criterion range] |
SUMIFS | adds numbers that meet multiple conditions | Math | numbers, criteria, [criteria range] |
SYD | returns one period of accelerated depreciation | Financial | cost, salvage value, life, period |
TIME | turns three numbers into a time | Time and Date | hour, minute, second |
TIMEVALUE | converts a time string to a fraction | Time and Date | time |
TODAY | returns the current date | Time and Date | none |
TYPE | returns an integer indicating the data type | Info | value |
UNIQUE | returns a table without duplicate lines | Filter | range |
WEEKDAY | returns the number of the of the week | Time and Date | date, [type] |
WEEKNUM | returns the week number of a day | Time and Date | date, [type] |
WORKDAY | calculates the workday a number of days away | Time and Date | start date, # of days, [holidays] |
WORKDAY.INTL | calculates the workday a number of days away | Time and Date | start date, # of days, [holidays], [weekends] |
YEAR | returns the date’s year value as a number | Time and Date | time |
YEARFRAC | calculates the number of years, including fractions between two dates | Time and Date | start date, end date, [day count convention] |
XLOOKUP | find values in a range by row | Lookup | lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode] |
COUNTBLANK | counts the number of blank cells | Math | values |
IF | evaluates a condition and returns a value | Logical | value, condition |
IFS | evaluates multiple conditions and returns a value | Logical | values, conditions |
TEXT | formats numerical data | Text | value, format |
CHAR | converts a number to a character | Text | number |
UNICHAR | converts a number to a character | Text | number |
CODE | finds a Unicode value | Text | text string |
HEX2DEC | converts a signed hexadecimal number to its decimal equivalent | Engineering | value |
INT | rounds an integer down to the nearest whole number | Math | number |
UNICODE | finds a Unicode value | Text | text string |
MOD | returns the remainder of division | Math | numbers |
ROUND | rounds a number to a specified number of decimal places | Math | value, [places] |
ROUNDDOWN | rounds a number down to a specified number of decimal places | Math | value, [places] |
ROUNDUP | rounds a number up to a specified number of decimal places | Math | value, [places] |
MROUND | rounds a number to the nearest multiple of a specified factor | Math | value, factor |
CEILING | rounds a number up to the nearest integer multiple of a specified significance | Math | value, factor |
FLOOR | rounds a number down to the nearest integer multiple of a specified significance | Math | value, factor |
TRUNC | removes unwanted digits from a number | Math | value, [places] |
CONCATENATE | combines text strings | Text | text strings |