MS Advance Excel Course

The PRODUCT function in Excel is used to multiply numbers together. It takes one or more arguments, separated by commas, that represent the numbers or ranges of cells that you want to multiply. The syntax of the PRODUCT function is: Syntax =PRODUCT(number1, [number2], …)   Notes 1. Numbers can be supplied individually or in ranges. 2. PRODUCT can accept up to 255 arguments total. 3. Only numbers in the array or reference are multiplied.  

The SUMPRODUCT function in Excel is used to multiply corresponding elements in two or more arrays, and then sum the results. It can be used to perform calculations on multiple arrays or to perform weighted averages. Syntax =SUMPRODUCT(array1, [array2], …) Note : that when using the SUMPRODUCT function, you must make sure that the arrays are the same size, otherwise you will get a #VALUE! error. Also, if any of the cells in the arrays contain text, logical values, or error values, they will be treated as 0 in the calculation. Example

The SUBTOTAL function in Excel is very helpful to analyze data of provided range. The SUBTOTAL Function allow to make filter and then perform various other Excel functions. Use the SUBTOTAL function to exclude filtered or hidden rows when calculating a total on the worksheet. Syntax: • function_num: a number that specifies the type of calculation to be performed. This can be any of the following values: 1 – AVERAGE 2 – COUNT 3 – COUNTA 4 – MAX 5 – MIN 6 – PRODUCT 7 – STDEV 8 – STDEVP 9 – SUM 10 – VAR 11 – VARP • ref1: the first range of cells to include in the calculation. • [ref2]: (optional) additional ranges of cells to include in the calculation. Example –

COUNTBLANK function is a formula used to count the number of empty cells within a specified range. It helps you determine how many cells in a given range do not contain any data, text, numbers, or formulas. Syntax  COUNTBLANK(range) range: This is the range of cells you want to count the blank cells within. It can be a single cell reference, a range reference, or an array.  

Count means to count or calculate. In the same way, count function is used in excel when we have to do counting from any Cells or Range and that too number type cells use Count function. The count function only considers number type cells for counting. Negative values, Percentages, Dates, Fractions, And Time considers all these numbers count function to count or calculate. But it ignores Empty cells and text values ​​and does not count them. Step 1: First of all, open the excel file in your computer in which you want to use the count function. Step 2: After opening, you have to select a cell in which cell we will apply the formula of count, as seen in the image below, it is highlighted with yellow color. Step 3: After selecting the cell, then apply the count formula, as shown in the image below. Example =COUNT(B2:F2) In this method, we have given the reference inside the formula which is from B2 to F2, then here the count function will count the number type cells from the given reference and will return the same. In the same way, we will apply the formula inside the rest of the cells as well.   #2 Method Count Function In Hindi In this method, we have not given the reference of any cells inside the formula, instead we have typed direct numbers in the formula itself as you can see in the image below. Note: For once if we use count function with text value then see what result comes If you see here, the count function has ignored the text value, that means it has not been counted, this is the reason that even after having the same reference, the result has changed this time. And here, if you remove the text value of a cell and store the number value in its place, then let’s see what happens and what the count function returns. So here we will see that the reference which has two text values ​​has result 3, and the reference which has one text value has result 4 and the reference which does not have text value has result 5.  

COUNTA function counts cells containing any type of information, including error values and empty text (“”). The COUNTA function does not count empty cells. Syntax COUNTA(value1, value2,….) As shown bellow in this example taken a scenario of seminar seat booking system in excel using COUNTA function. Function : =COUNTA(A3:S15)

MATCH() returns the position of a cell in a row or column. The Excel MATCH function searches for a lookup value in a range of cells and returns the relative position of that value in the range. Syntax MATCH(lookup_value, lookup_array, [match_type]) Arguments 1. lookup_value – the number or text value you are looking for. 2. lookup_array – a range of cells being searched. 3. match_type – specifies whether to return an exact match or the nearest match: find row index position number using Match find Column index position number using Match Example  

INDEX() returns the value of a cell in a table based on the column and row number. The INDEX function in Excel is fantastically flexible and powerful, and you’ll find it in a huge number of Excel formulas, especially advanced formulas. Syntax INDEX(array, row_num, [column_num]) Arguments 1. array – a range of cells that you want to return a value from. 2. row_num – the row number in array from which you want to return a value. 3. column_num – the column number in array from which you want to return a value.

The INDEX MATCH Formula is the combination of two functions in Excel which is INDEX and MATCH. INDEX and MATCH is the most popular function in Excel for performing more advanced lookups. INDEX() returns the value of a cell in a table based on the column and row number. MATCH() returns the position of a cell in a row or column. The INDEX Function The INDEX function in Excel is fantastically flexible and powerful, and you’ll find it in a huge number of Excel formulas, especially advanced formulas. Syntax INDEX(array, row_num, [column_num]) Arguments 1. array – a range of cells that you want to return a value from. 2. row_num – the row number in array from which you want to return a value. 3. column_num – the column number in array from which you want to return a value. Example 1    MATCH function in Excel The Excel MATCH function searches for a lookup value in a range of cells and returns the relative position of that value in the range. Syntax MATCH(lookup_value, lookup_array, [match_type]) Arguments 1. lookup_value – the number or text value you are looking for. 2. lookup_array – a range of cells being searched. 3. match_type – specifies whether to return an exact match or the nearest match: find row index position number using Match find Column index position number using Match Example   How to use INDEX MATCH function in Excel? INDEX MATCH is a clever way to perform a two-way lookup in Excel by combining the power of the INDEX and MATCH functions.

The AVERAGEIFS function in Excel is similar to AVERAGEIF but allows you to find the average of a range of cells that meet multiple criteria. The function syntax is as follows: Syntax =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) • average_range: the range of cells to calculate the average for. • criteria_range1: the first range of cells to evaluate against the first criteria. • criteria1: the first condition or criteria that must be met. • [criteria_range2, criteria2]: (optional) additional ranges of cells and conditions to evaluate. You can specify up to 127 range/criteria pairs in the formula.