SUM(range)
Use: Adds all numbers in a specified range. Adds values from cell A1 to A10.
=SUM(A1:A10)
AVERAGE(range)
Use: Calculates the mean of numbers in a range. Finds the average of values in cells B1 to B10.
=AVERAGE(B1:B10)
COUNT(range)
Use: Counts the number of cells with numerical data in a range. Counts how many cells have numbers in C1 to C10.
=COUNT(C1:C10) COUNTA(range)
Use: Counts non-empty cells in a specified range. Counts all non-empty cells in the range D1 to D10.
=COUNTA(D1:D10) IF(logical_test, value_if_true, value_if_false)
Use: Performs a logical test and returns one value if true, and another if false. Checks if E1 is greater than 5, returns “High” if true, “Low” if false.
=IF(E1>5, "High", "Low")AND(logical1, [logical2], …)
Use: Evaluates multiple conditions and returns TRUE if all are true. Checks if F1 is greater than 5 and F2 is less than 10.
=AND(F1>5, F2<10)OR(logical1, [logical2], …)
Use: Checks multiple conditions and returns TRUE if any are true. Returns TRUE if G1 is 10 or G2 is 20.
=OR(G1=10, G2=20)
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Use: Searches for a value in the first column of a table and returns a value in the same row from a specified column. Looks for H1’s value in the first column of A1:B10 and returns the corresponding value from the second column.
=VLOOKUP(H1, A1:B10, 2, FALSE)HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Use: Searches for a value in the first row and returns a value in the same column from a specified row. Looks for I1’s value in the first row of A1:J2 and returns the corresponding value from the second row.
=HLOOKUP(I1, A1:J2, 2, FALSE)INDEX(array, row_num, [column_num])
Use: Returns a value specified by row and column numbers within an array. Gets the value in the fifth row and second column of range A1:B10.
=INDEX(A1:B10, 5, 2)MATCH(lookup_value, lookup_array, [match_type])
Use: Searches for a specified item in a range and returns its relative position. Finds “Item” in the range A1:A10 and returns its position.
=MATCH("Item", A1:A10, 0)SUMIF(range, criteria, [sum_range])
Use: Adds the cells specified by a given condition. Sums the values in B1:B10 where the corresponding cells in A1:A10 are greater than 5.
=SUMIF(A1:A10, ">5", B1:B10) CONCATENATE(text1, [text2], …)
Use: Joins two or more text strings into one string. Combines the text in A1 and B1 with a space between.
=CONCATENATE(A1, " ", B1) LEFT(text, [num_chars])
Use: Returns the specified number of characters from the start of a text string. Gives the first three characters in A1.
=LEFT(A1, 3)RIGHT(text, [num_chars])
Use: Returns the specified number of characters from the end of a text string.
=RIGHT(A1, 3)