46 functions for this use case · out of 12 defined
How to use the SUM function in Excel to add individual values, cell references, ranges, or a mix of all three.
How to use the IF function in Excel to make logical comparisons between data values.
Returns the largest value in a set of values.
Returns the value of an element in a table or an array, selected by the row and column number indexes. Use the array form if the first argument to INDEX is an array constant. Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of non-adjacent selections, you can pick the selection to look in.
Returns the smallest number in a set of values.
Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.
Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays.
Learn how to use function VLOOKUP in Excel to find data in a table or range by row. Our step-by-step guide makes vlookup in excel easy and efficient.
How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria.
Use the AND function, one of the logical functions, to determine if all conditions in a test are TRUE.
How to use the COUNTIFS function in Excel to apply criteria to cells, across multiple ranges, and count the number of times all criteria are met.
Rounds a number up, away from 0 (zero).
The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. IFS will allow you to include up to 127 pairs of conditions (the test if something is True and the result if something is true). IFS can take the place of multiple nested IF statements.
LEN returns the number of characters in a text string.
Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.
Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
How to use the COUNT function in Excel to count the number of cells that contain numbers, and to count numbers within a list of arguments.
How to use the FILTER function in Excel to filter a range of data based on criteria you define.
MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains 23.7825, and you want to round that value to two decimal places, you can use the following formula: =ROUND(A1, 2) The result of this function is 23.78.
Use Excel's DATE function when you need to take three separate values and combine them to form a date.
TEXTSPLIT function
The COUNTA function counts the number of cells that are not empty in a range.
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
Conditional count (a single condition)
Calculates the number of days, months, or years between two dates.
How to use the IFERROR function in Excel to trap and handle errors in a formula.
The TODAY function returns the serial number of the current date. The serial number is the date-time code used by Excel for date and time calculations. If the cell format was General before the function was entered, Excel changes the cell format to Date. If you want to view the serial number, you must change the cell format to General or Number. The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals. For example, if you know that someone was born in 1963, you might use the following formula to find that person's age as of this year's birthday: = YEAR( TODAY())-1963 This formula uses the TODAY function as an argument for the YEAR function to obtain the current year, and then subtracts 1963, returning the person's age.
Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It's useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.
Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
LEFT returns the first character or characters in a text string, based on the number of characters you specify.
Use the OR function, one of the logical functions, to determine if any conditions in a test are TRUE.
Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
STOCKHISTORY function
How to use the MATCH function in Excel to search for a specified item in a range of cells, returning the relative position of that item in the range.
Learn about the SORT function, which sorts the contents of a range or array. SORT is in a class of functions called dynamic arrays.
How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.
RIGHT returns the last character or characters in a text string, based on the number of characters you specify.
Returns the average (arithmetic mean) of all cells that meet multiple criteria.
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. This function is provided for backward compatibility; consider using the NORM.INV function instead.
The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide delimiter or IgnoreEmpty arguments.
Retrieves real-time data from a program that supports COM automation.
Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. If you were to sort the list, the rank of the number would be its position.