Continuing with the previous post on excel functions , in this article we will learn few more important functions.

## Important Excel Functions

--------------------------------------------------------------------------------------------------------------------

**IFERROR**

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors produced by other formulas or functions. IFERROR checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

**SYNTAX**

=IFERROR (value, value_if_error)

For example, if A1 contains 10, B1 is blank, and C1 contains the formula =A1/B1, the following formula will catch the #DIV/0! error that results from dividing A1 by B1:

=IFERROR (A1/B1,"Please enter a value in B1")

----------------------------------------------------------------------------------------------------

**COUNTIFS**

The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

**SYNTAX**

=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

The difference between countif and countifs is that while countif is used to count the cells that meets a single criteria, countifs is used to count the cells that meets multiple criteria.

If you have an older version of Excel that doesn't have the COUNTIFS function, you can use the COUNTIF function instead like this:

=COUNTIF(range,">=X")-COUNTIF(range,">Y")

----------------------------------------------------------------------------------------

**SUM/ SUMIF / SUMIFS**

**SUM** function is used to add the values in a range

You use the **SUMIF** function to sum the values in a range that meet criteria that you specify.

The **SUMIFS** function adds all arguments that meet multiple criteria

**SYNTAX**

=SUM(range)

For example, the formula **=SUM(C2:C5)** adds the values in range C2:C5.

=SUMIF(range, criteria, [sum_range])

For example, the formula **=SUMIF(B2:B5, "John", C2:C5)** sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

Example for SUMIFS with multiple criteria and OR logic is as below

---------------------------------------------------------------------------------------------------------------------------------------

**SUMPRODUCT**

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.

The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 arrays can be supplied.

**SYNTAX**

=SUMPRODUCT (array1, [array2], ...)

The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 arrays can be supplied.

We can use this function along with SUM function to get **WEIGHTED AVERAGE**.

**Notes:**

- SUMPRODUCT treats non-numeric items in arrays as zeros.
- Array arguments must must be the same size. Otherwise, SUMPRODUCT will generate a #VALUE! error value.
- Logical tests inside arrays will create TRUE and FALSE values. In most cases, you'll want to coerce these to 1's and 0's

-------------------------------------------------------------------------------------------------------------

**AVERAGE / AVERAGEIF/ Average of top/bottom N values**

To get the average of a set of numbers, use the **AVERAGE** function.

To get the average of a set of numbers, with some criteria, use the **AVERAGEIF** function.

To average the top n scores in a data set, you can use a **AVERAGE** formula with **LARGE **function.

**SYNTAX**

=AVERAGE(RANGE)

For example, to get the average of values in range C2:C5, use **=AVERAGE(C2:C5)**

=AVERAGEIF(RANGE, Criteria)

For example, to get the average of values, excluding Zero values in range C2:C5, use **=AVERAGEIF(C2:C5, "<>0")**

=AVERAGE(LARGE(range,{1,2,..,n}))

For example, to average the top 3 scores in a data set, use **=AVERAGE(LARGE(B6:F6,{1,2,3}))**

-----------------------------------------------------------------------------------------------------------

**INDEX AND MATCH**

The Excel **INDEX **function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns.

**SYNTAX:**

=INDEX (array,row number)

**MATCH** is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches.

**SYNTAX**

=MATCH (lookup value,lookup array,match type)

INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

**INDEX MATCH Vs VLOOKUP**

VLOOKUP requires more processing power from Excel because it needs to evaluate the entire table array you’ve selected. With INDEX MATCH, Excel only has to consider the lookup column and the return column. With fewer absolute cells to consider, Excel can process this formula much faster.

**SYNTAX:**

=INDEX(Array, MATCH formula)

Please watch this section to see more on Excel functions and other Advanced Features.