1. SUM
This is the first Excel Function one should be familiar with. It basically performs the addition operation. The Syntax in which it is used is as follows:
SUM (number1, [number2], …)
Generally, arguments in square brackets are optional, the other ones are required.
See it used in the example below
2. SUMIF
This is an important Excel Function that is utilized in the everyday use of Microsoft Excel. It is a conditional SUM. The Syntax in which it is used is as follows:
SUMIF (range, criteria, [sum_range])
Range – This is the range of cells that contains the criteria to be summed
Criteria – This the criteria or conditions that must be met
Sum_range – The range of cells to be summed if conditions specified are met.
See the example below in the SUMIFS session. In the Example, the objective is to sum the Ages of people with a Grade “A”.
3. SUMIFS
This is used the same way SUMIF is used, but this is when you have more than one criteria you need to specify. The Syntax in which it is used is as follows:
SUMIFS (sum_range, criteria_range1, criteria1,[criteria_range2, criteria2],…)
Sum_range – The range of cells to be summed if conditions specified are met.
Criteria_range1 – This is the range of cells in which the criteria to be specified will be tested on
Criteria1 – This the criteria or conditions that must be met
See an example of its usage below:
In this example, the objective was to determine the sum of Ages of individuals named “Peter” with Grade “C”
4. COUNT
This Excel function is used to count the number of cells in a range that contains Numbers. The Syntax in which it is used is as follows:
=COUNT (value1, [value2], …)
5. COUNTA
This Excel function returns the number of cells that contain texts, contain errors, and logical values. Basically, it returns the number of cells in a range that is not empty.
The Syntax in which it is used is as follows:
=COUNTA (value1, [value2], …)
6. COUNTBLANK
This Excel function is used to count the number of empty cells. The Syntax in which it is used is as follows:
=COUNTBLANK (range)
7. COUNTIF
This is an Excel Function that counts the number of cells that meet a criterion, criteria which are also specified in the formula. The Syntax in which it is used is as follows:
=COUNTIF (range, criteria)
The formula allows for a logical operator like greater than (>), less than (<), equal to (=), not equal to (<>) & wildcards (*,?) which helps to define partial matchings.
8. COUNTIFS
This Excel Function counts the cells that meet a set of criteria or multiple criteria. The Syntax in which it is used is as follows:
=COUNTIFS (range1, criteria1, [range2], [criteria2], …)
The formula also allows for logical operators like greater than (>), less than (<), equal to (=), not equal to (<>) & wildcards (*,?) which help to define partial matchings.
9. FIND
This Function evaluates and returns the position of a text inside another text. The location is expressed as a number. The Syntax in which it is used is as follows:
=FIND (find_text, within_text, [start_num])
Find_text – This is the text being searched for
Within_text – Cell reference where the search is to be done
[Start_num] – This specifies the number at which the search will be conducted. This is optional. It is used when the text being searched for occurs more than once in the within_text
10. LEN
This Excel Function returns the number of characters in a cell. It also counts [spaces]. The Syntax in which it is used is as follows:
=LEN (text)
Here’s an example of its usage
11. AVERAGE
This Excel function returns the average of a range of cells. This function works the same way as the SUM Function does. The Syntax in which it is used is as follows:
AVERAGE (number1, [number2], …)
12. AVERAGEIF
The function returns the average of a range of cells based on a condition specified. It works exactly like SUMIF only this gives the average as against sum The Syntax in which it is used is as follows:
AVERAGEIF(range, criteria, [sum_range])
See the example below:
13. AVERAGEIFS
This Excel function returns the average of a range of cells based on multiple conditions. It works the same way SUMIFS works, only this gives an average as against sum. The Syntax in which it is used is as follows:
AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
See below for an illustration of how it is used:
14. VLOOKUP
Basically, what this Excel Function does is to Look-up or search for a value or text specified in a table array and returns a value or text from another column, also specified. The “V” stands for Vertical. The Syntax in which it is used is as follows:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
1.) Lookup_value: It is the value being looked up. It can be a text (the text must be in quoted commas), number, or a cell reference.
2.) table_array: It is the table that contains the value being looked up. The column containing the lookup value must be the first column in this array.
3.) col_index_num: The is the column number in the above table_array which contains the value to be returned.
4.) [range_lookup]: Here is where you specify whether you are looking for an Exact match (FALSE or 0) or an approximate match (TRUE or 1). This field is not compulsory. The default option is FALSE
15. HLOOKUP
This Function Horizontal counterpart of VLOOKUP. It searches the first row of the array and returns the value in the column from the row specified. The Syntax in which the function is used is as follows:
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]
1.) Lookup_value: It is the value being looked up. It can be a text (the text must be in quoted commas), number, or a cell reference.
2.) table_array: It is the table that contains the value being looked up. The column containing the lookup value must be the first column in this array.
3.) row_index_num: The is the column number in the above table_array which contains the value to be returned.
4.) [range_lookup]: Here is where you specify whether you are looking for an Exact match (FALSE or 0) or an approximate match (TRUE or 1). This field is not compulsory. The default option is FALSE
See the example below:
16. MONTH
This Excel Function returns the Month Number of a Date. A date in January will return “1”, a date in September will return “9”. The Syntax in which the function is used is as follows:
=MONTH (serial_number)
See the example below:
17. WEEKNUM
This returns the week number of a date. The Syntax in which the function is used is as follows:
=WEEKNUM (serial_number, [return_type])
See the example below:
18. WEEKKDAY
This Excel Function returns from 1 to 7 identifying the day of the week of a date. The Syntax in which the function is used is as follows:
=WEEKDAY (serial_number, [return_type])
See the example below:
19. YEAR
This Excel function returns the Year of a Date. The Syntax in which the function is used is as follows:
=YEAR (serial_number)
20. TRIM
This Excel function removes all spaces from a text string except for single spaces between words. The Syntax in which the function is used is as follows:
=TRIM(text)
See the example below: