Advance Excel

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 […]

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

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

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

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.

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

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

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

The AVERAGEIF function in Excel is used to find the average of a range of cells that meet a certain condition. Syntax • range: the range of cells to evaluate against the criteria. • criteria: the condition or criteria that must be met. This can be a number, text, date, logical expression or cell reference.