Microsoft Excel Basic Formulas List

Microsoft Excel Basic Formulas List

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

clip_image002[1]

 

2. SUMIF

This is an important Excel Function that is utilized in 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 which contains the criteria to be summed

Criteria – This the criteria or condition that must be met

Sum_range – The range of cells to be summed if conditions specified are met.

See example below in SUMIFS session. In the Example, the objective is to sum the Ages of people with 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 condition that must be met

See an example of it usage below:

clip_image004[1]

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 range that contain Numbers. The Syntax in which it is used is as follows:

=COUNT (value1, [value2], …)

clip_image006[1]

 

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 are not empty.

The Syntax in which it is used is as follows:

=COUNTA (value1, [value2], …)

clip_image008[1]

 

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)

clip_image010[1]

 

7. COUNTIF

This is an Excel Function that counts the number of cells that meet a criterion, criteria which is also specified in the formula. The Syntax in which it is used is as follows:

=COUNTIF (range, criteria)

The formula allows for logical operator like greater than (>), less than (<), equal to (=), not equal to (<>) & wildcards (*,?) which helps to define partial matchings.

clip_image012[1]

 

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 operator like greater than (>), less than (<), equal to (=), not equal to (<>) & wildcards (*,?) which helps to define partial matchings.

clip_image014[1]

 

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])

clip_image015[1]

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 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

clip_image016[1]

 

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], …)

clip_image018[1]

 

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 example below:

clip_image020[1]

 

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:

clip_image022[1]

 

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 the table that contains the value being looked up. The column containing the look-up 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

clip_image024[1]

 

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 the table that contains the value being looked up. The column containing the look-up 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 example below:

clip_image025[1]

 

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 example below:

clip_image027[1]

 

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 example below:

clip_image029[1]

 

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 example below:

clip_image031[1]

 

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)

clip_image033[1]

 

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 example below:

clip_image034[1]