Excel Functions Flashcards

Adds up all numbers in a specified range of cells. Syntax: =SUM(range).

Adds numbers in a range based on a single criterion. Syntax: =SUMIF(range, criteria, [sum_range]).

Like Sumif, but allows for multiple criteria. Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

Specific conditions that determine which cells to add, like a certain text or numerical value.

The cells whose values are to be added in Sumif or Sumifs functions.

The range of cells where Excel looks for the specified criteria in Sumif or Sumifs functions.

Counts cells in a range that contain numbers. Syntax: =COUNT(range).

Counts all non-empty cells (including text and numbers). Syntax: =COUNTA(range).

Counts cells meeting a single specified condition. Syntax: =COUNTIF(range, criteria).

Similar to Countif, but counts cells meeting multiple conditions.

Conditions based on numerical values like greater than or less than a specified number.

Conditions based on specific text strings in cells for counting.

Ability to use multiple ranges and criteria for complex counting tasks.

How to specify conditions as text strings within quotation marks in COUNTIF and COUNTIFS functions.

Retrieves data from a column in a table based on a matching value. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The value in Excel VLOOKUP used to find the corresponding data in a table

The range of cells containing the data to be retrieved by VLOOKUP

The column number in the table array from which to retrieve data

Specifies whether VLOOKUP should find an exact match (FALSE) or an approximate match (TRUE)

Similar to VLOOKUP, but retrieves data based on a row instead of a column. Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Retrieves a value from a specified position within a range. Syntax: =INDEX(array, row_num, [column_num])

The range of cells where the Index function searches for the specified value

Specifies the exact position in the array to retrieve the value from

Returns the relative position of an item in a range matching a specified value. Syntax: =MATCH(lookup_value, lookup_array, [match_type])

The value for which Match searches within the array

The range of cells where Match looks for the lookup value

Determines whether Match finds an exact match (0) or the closest match

Calculates the arithmetic mean of a range of numbers. Syntax: =AVERAGE(number1, [number2], ...)

Computes the average of numbers in a range based on a specific criterion. Syntax: =AVERAGEIF(range, criteria, [average_range])

Identifies the highest value in a specified range of numbers. Syntax: =MAX(number1, [number2], ...)

Determines the lowest value in a given array of numbers. Syntax: =MIN(number1, [number2], ...)

Selects a value from a list based on a specified position number. Syntax: =CHOOSE(index_num, value1, [value2], ...)

A "What-if" analysis tool in Excel for finding required input values to achieve a specific goal. Part of the Data tab under What-if Analysis

Extracts a specified number of characters from the start of a text string. Syntax: =LEFT(text, num_chars)

Retrieves a specified number of characters from the end of a text string. Syntax: =RIGHT(text, num_chars)

Selects characters from a text string starting at a specified position. Syntax: =MID(text, start_num, num_chars)

Converts all characters in a text string to uppercase. Syntax: =UPPER(text)

Transforms all characters in a text string to lowercase. Syntax: =LOWER(text)

Capitalizes the first letter of each word in a text string. Syntax: =PROPER(text)

Joins multiple text strings into one string. Syntax: =CONCATENATE(text1, [text2], ...)

Adjusts a number to a specified number of decimal places. Syntax: =ROUND(number, num_digits)

Manages errors by replacing them with a specified value. Syntax: =IFERROR(value, value_if_error)

Evaluates multiple conditions and returns TRUE if any one of them is true. Syntax: =OR(logical1, [logical2], ...)