Essential Functions

This is our list of essential functions. These are the ones you should learn first.
There are over 470 built-in worksheet functions, but these ones are the most common.


1) The IF function.
Returns the value based on whether a certain condition is either True or False.

=IF(A1 > 100, "Display True", "Display False") 

2) The IFS function.
Returns the value based on multiple True or False conditions.

IFS(logical_text1, value_if_true1 [,logical_text2, value_if_true2] [..]) 

3) The SUM function.
Returns the total value of the numbers in a list, table or cell range.

SUM(number1 [,number2] [..]) 

4) The SUMIFS function.
Returns the total value of the numbers that satisfies multiple conditions.
Often referred to as conditional summing.

SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..]) 

5) The COUNTIFS function.
Returns the number of non blank cells that satisfies multiple conditions.
Often referred to as conditional counting.

COUNTIFS(criteria_range1, criteria1 [,criteria_range2, criteria2] [..]) 

6) The AVERAGEIFS function.
Returns the arithmetic mean of all the numbers in a range that satisfies multiple conditions.

AVERAGEIFS(average_range, criteria_range1, criteria1 [,criteria_range2, criteria2]) 

7) The MINIFS function.
Returns the smallest value in a list or array of numbers that satisfies multiple conditions.

MINIFS(min_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..]) 

8) The MAXIFS function.
Returns the largest value in a list or array of numbers that satisfies multiple conditions.

MAXIFS(max_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..]) 

9) The CONCAT function.
Returns the text string that is a concatenation of cell ranges and strings.
This function is equivalent to the ampersand (&) character and can be used to join several strings together.

CONCAT(text1 [,text2] [..]) 

10) The INDEX function.
Returns the value from a cell range which is the intersection of a row and a column.

INDEX(reference, row_num [,column_num] [,area_num]) 

11) The MATCH function.
Returns the position of a value in a list, table or cell range.

MATCH(lookup_value, lookup_array [,match_type]) 

12) The VLOOKUP function.
Returns the value in the same row after finding a matching value in the first column.

VLOOKUP(lookup_value, table_array, col_index_num [,range_lookup]) 

13) The XLOOKUP function.
Returns the value in the same row after finding a matching value in any column.
Added in Excel 365 to replace the VLOOKUP function.

XLOOKUP(lookup_value, lookup_array, return_array [,if_not_found] [,match_mode] [,search_mode]) 

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext