Microsoft Excel: Formulas and Functions

SUM

1. The SUM() function gives the total of selected range of cell values. It performs the mathematical operation which is addition. 
   
   
2. Syntax: "=SUM(C2:C4)" 
   
   
   
   
   

AVERAGE

1. The AVERAGE() function calculates the average of the selected range of cell values.  
   
   
2. Syntax: “=AVERAGE(C2, C3, C4)”   
   
   
   
   

COUNT

1. The function COUNT() counts the total number of cells in a range that contains a number. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric.
   
   
2. Syntax: "=COUNT(C1:C4)"  
   
   
   
   
3. If you are required to count all the cells with numerical values, text, and any other data format, you must use the function COUNTA(). However, COUNTA() does not count any blank cells.
   
   
4. To count the number of blank cells, present in a range of cells, COUNTBLANK() is used.  
   
   

SUBTOTAL

1. The SUBTOTAL() function returns the subtotal in a database. Depending on what you want, you can select either average, count, sum, min, max, etc. 
   
   
2. Syntax: “=SUBTOTAL(1, A2: A4)" 
   
   
   
   
   

CONCATENATE

1. This function merges or joins several text strings into one text string.  
   
   
2. Syntax: "=CONCATENATE(A1, " ", B1, " ", C1)" 
   
   
   
   

VLOOKUP

1. You can use VLOOKUP when you need to find things in a table or a range by row.
   
   
2. Syntax: " =VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)) ".
   
   Example 1:
   
   
   
   Example 2:
   
   

HLOOKUP

1. You can use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows.  
   
   
2. Syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) 
   
   
3. HLOOKUP function consists of 4 components: 
   a. The value you want to look up; 
   
   b. The range in which you want to find the value and the return value;
   
   c. The number of the row within your defined range, that contains the return value; 
   
   d. 0 or FALSE for an exact match with the value you are looking for; 1 or TRUE for an approximate match.