Formulas in Excel are codes that give results based on provided inputs and applied logic. Once you become dependent on Microsoft Excel for your daily office work, after some time you will start feeling something is missing that can increase your working speed and productivity. That missing thing is advanced Excel formulas.
Advanced Excel formulas are not as difficult as we think. We just have to understand the logic behind it. Learning more advanced Excel formulas will improve your advanced Excel skills. Such advanced Excel skills will make you recognized even after standing in a crowd.
In this special article, we will see more than 10 advanced Excel formulas along with their example. In the end, there will be productivity tips to use those advanced formulas.
🦘 Jump to the following Excel formula,
List of Top Advanced Excel Formulas & Functions
Advanced Excel formulas are little complicated formulas that can process a variety of data with complex logic. Advanced formulas save lots of time. They allow more functionalities and possibilities to process information and drive results.
Here are more than 20 advanced Excel formulas to improve the productivity of your office work. They are easy and listed with examples.
IF formula
The IF formula in Excel is a powerful function that allows you to make decisions based on specific conditions. It evaluates a given condition and returns different values depending on whether the condition is true or false. This enables you to perform conditional calculations and automate decision-making processes within your spreadsheets. The syntax of the formula is as :
=IF(logical_test, value_if_true, value_if_false)logical_test:This is the condition or logical expression that you want to evaluate.value_if_true:This is the value that will be returned if the logical_test evaluates to true.value_if_false:This is the value that will be returned if the logical_test evaluates to false.
Example: Suppose you have a spreadsheet with student scores in column A, and you want to assign a pass or fail status based on a passing score of 70. You can use the IF formula to achieve this:
=IF(A2 >= 70, "Pass", "Fail")In this example, the formula checks if the score in cell A2 is greater than or equal to 70. If it is, it will return “Pass”; otherwise, it will return “Fail”.
INDEX and MATCH formulas
The INDEX and MATCH formulas in Excel are frequently used together to perform powerful lookups and retrieve data from a table. While the VLOOKUP and HLOOKUP functions are limited to searching for values in a single row or column, the INDEX and MATCH combination offers more flexibility by allowing searches in multiple columns or rows. Here’s an explanation of each formula and an example of how they can be used together:
INDEX formula
The INDEX formula returns the value of a cell within a specified range based on its row and column numbers. It allows you to extract data from a table by specifying the row and column references. Syntax is:
=INDEX(array, row_num, [column_num])array:This is the range or array from which you want to retrieve the value.row_num:This is the row number within the array where the desired value is located.column_num:(Optional) This is the column number within the array where the desired value is located. If omitted, INDEX returns the entire row specified by the row_num.
Example: Suppose you have a table with student names in column A and their corresponding scores in column B. To retrieve the score for a specific student, you can use the INDEX formula as follows:
=INDEX(B:B, 3)This formula will return the value from the third row of column B, which corresponds to the score of the student in the third row of column A.
MATCH formula
The MATCH formula searches for a specified value within a range and returns the relative position of that value. It is commonly used to find the position of a lookup value in a column or row.
Syntax: =MATCH(lookup_value, lookup_array, [match_type])lookup_value: This is the value you want to find within the lookup_array.lookup_array: This is the range or array where you want to search for the lookup_value.match_type: (Optional) This determines the type of match to be performed: 1 for an exact match (default), 0 for an exact match or next smallest value, and -1 for an exact match or next largest value.
Example: Continuing with the student table example, let’s say you want to find the position of a specific student’s name in the range A2:A10. You can use the MATCH formula like this:
=MATCH("John", A2:A10, 0)This formula will search for the name “John” within the range A2:A10 and return the position (row number) where it is found.
Combining INDEX and MATCH
Now, let’s combine the INDEX and MATCH formulas to retrieve the score of a specific student by matching their name.
Example: Suppose you want to retrieve the score for the student named “John” from the student table. You can use the following formula:
=INDEX(B:B, MATCH("John", A:A, 0))This formula first uses the MATCH function to find the position of “John” within the range A:A (student names). Then, the INDEX function retrieves the corresponding value from column B (scores) using the matched position.
The combination of INDEX and MATCH allows for more flexible and dynamic lookups in Excel, as it can search for values horizontally or vertically and retrieve data from any column or row in a table. It is particularly useful when dealing with large datasets or when the lookup values are not in the first column or row of the table.
SUMIF formula
The SUMIF formula in Excel allows you to calculate the sum of values in a range that meets specific criteria. It is useful for analyzing data and performing calculations based on certain conditions. The syntax of the SUMIF formula is as follows:
=SUMIF(range, criteria, [sum_range])range: This is the range of cells that you want to evaluate against the criteria. It can be a single column or row, or a combination of both.criteria: This is the condition or criteria that the cells in the range should meet in order to be included in the calculation. It can be a number, text, logical expression, cell reference, or wildcard characters.sum_range: (Optional) This is the range of cells that you want to sum if the corresponding cells in the range meet the criteria. If omitted, the formula will sum the cells in the range that meet the criteria.
Example: Suppose you have a dataset with sales data in column A and corresponding product names in column B. You want to calculate the total sales for a specific product. You can use the SUMIF formula to achieve this:
=SUMIF(B:B, "Product A", A:A)In this example, the formula checks each cell in column B for the value “Product A”. It then adds up the corresponding values from column A for the cells that match the criteria.
You can also use operators and wildcards in the criteria to perform more advanced calculations. For example, if you want to sum the sales for products that start with the letter “A”, you can use the following formula:
=SUMIF(B:B, "A*", A:A)In this case, the asterisk (*) acts as a wildcard character, allowing any value that starts with “A” to be included in the sum.
COUNTIF formula
The COUNTIF formula in Excel allows you to count the number of cells in a range that meet specific criteria. It is a useful function for analyzing data and determining the frequency of occurrences based on certain conditions. The syntax of the COUNTIF formula is as follows:
=COUNTIF(range, criteria)Here’s an explanation of each component of the COUNTIF formula:
- range: This is the range of cells that you want to evaluate against the criteria. It can be a single column or row, or a combination of both.
- criteria: This is the condition or criteria that the cells in the range should meet in order to be counted. It can be a number, text, logical expression, cell reference, or wildcard characters.
Example: Suppose you have a dataset with student scores in column A, and you want to count the number of students who scored above 80. You can use the COUNTIF formula to achieve this:
=COUNTIF(A:A, ">80")In this example, the formula checks each cell in column A and counts the number of cells that contain a value greater than 80.
You can also use operators and wildcards in the criteria to perform more advanced calculations. For example, if you want to count the number of students whose names start with the letter “J”, you can use the following formula:
=COUNTIF(B:B, "J*")In this case, the asterisk (*) acts as a wildcard character, allowing any value that starts with “J” to be counted.
VLOOKUP formula
The VLOOKUP formula in Excel is a powerful function that allows you to search for a specific value in the leftmost column of a table and retrieve a corresponding value from a different column in the same row. It is commonly used for data lookup and retrieval tasks. The syntax of the VLOOKUP formula is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Here’s an explanation of each component of the VLOOKUP formula:
- lookup_value: This is the value you want to search for in the leftmost column of the table. It can be a number, text, logical expression, or a cell reference.
- table_array: This is the range of cells that represents the table from which you want to retrieve the value. It should include the column where the lookup value is located and the column(s) from which you want to retrieve the corresponding value(s).
- col_index_num: This is the column number (counting from the leftmost column in the table_array) from which you want to retrieve the value. For example, if you want to retrieve the value from the third column, the col_index_num would be 3.
- range_lookup: (Optional) This is a logical value that determines the type of match to be performed. Use TRUE or omitted for an approximate match (sorted data required), or use FALSE for an exact match.
Example: Suppose you have a table with product names in column A and their corresponding prices in column B. You want to find the price of a specific product by searching for its name in the table. You can use the VLOOKUP formula to achieve this:
=VLOOKUP("Product A", A:B, 2, FALSE)In this example, the formula searches for “Product A” in the leftmost column of the range A:B (the table). Once it finds a match, it retrieves the corresponding value from the second column (price column).
HLOOKUP formula
The HLOOKUP formula in Excel is similar to the VLOOKUP formula but is used to perform horizontal lookups. It allows you to search for a specific value in the top row of a table and retrieve a corresponding value from a different row in the same column. The HLOOKUP function is useful for data lookup and retrieval tasks where the data is organized horizontally. The syntax of the HLOOKUP formula is as follows:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Here’s an explanation of each component of the HLOOKUP formula:
- lookup_value: This is the value you want to search for in the top row of the table. It can be a number, text, logical expression, or a cell reference.
- table_array: This is the range of cells that represents the table from which you want to retrieve the value. It should include the row where the lookup value is located and the row(s) from which you want to retrieve the corresponding value(s).
- row_index_num: This is the row number (counting from the top row in the table_array) from which you want to retrieve the value. For example, if you want to retrieve the value from the third row, the row_index_num would be 3.
- range_lookup: (Optional) This is a logical value that determines the type of match to be performed. Use TRUE or omitted for an approximate match (sorted data required), or use FALSE for an exact match.
Example: Suppose you have a table with product names in the top row (row 1) and their corresponding prices in the second row. You want to find the price of a specific product by searching for its name in the top row of the table. You can use the HLOOKUP formula to achieve this:
=HLOOKUP("Product A", A1:D2, 2, FALSE)In this example, the formula searches for “Product A” in the top row of the range A1:D2 (the table). Once it finds a match, it retrieves the corresponding value from the second row (price row).
NETWORKDAYS formula
The NETWORKDAYS formula in Excel is used to calculate the number of working days between two specified dates, excluding weekends and optionally, specified holidays. It is commonly used for tracking project durations, calculating employee attendance, and determining turnaround times. The syntax of the NETWORKDAYS formula is as follows:
=NETWORKDAYS(start_date, end_date, [holidays])Here’s an explanation of each component of the NETWORKDAYS formula:
- start_date: This is the starting date from which you want to calculate the number of working days. It can be a date value or a reference to a cell containing a date.
- end_date: This is the ending date up to which you want to calculate the number of working days. It can be a date value or a reference to a cell containing a date.
- holidays: (Optional) This is an optional range or array of dates that represent holidays or non-working days. You can specify a single range of cells or manually enter the holiday dates.
Example: Suppose you have a project with a start date of 1st June 2023 and an end date of 10th June 2023. You want to calculate the number of working days between these two dates, excluding weekends (Saturdays and Sundays) as well as a specified holiday on 7th June 2023. You can use the NETWORKDAYS formula to achieve this:
=NETWORKDAYS(DATE(2023, 6, 1), DATE(2023, 6, 10), DATE(2023, 6, 7))In this example, the formula calculates the number of working days between 1st June 2023 and 10th June 2023, excluding 7th June 2023 as it is specified as a holiday. The result will be the count of weekdays (working days) within the specified range, excluding weekends and the specified holiday.
The NETWORKDAYS formula in Excel provides a convenient way to calculate the number of working days between two dates, taking into account weekends and optional holidays. It is a useful tool for time-sensitive calculations and project management. By utilizing this formula, you can accurately determine the duration of tasks, track employee attendance, and plan project timelines more effectively.
TODAY and NOW functions
The TODAY and NOW functions in Excel are used to retrieve the current date and time, respectively, from the system clock of your computer. These functions are particularly useful in dynamic calculations, where you need to capture the current date or time and have it automatically updated whenever the worksheet is recalculated. Here’s an explanation of each function:
TODAY function:
The TODAY function returns the current date as a serial number in Excel’s date format. It does not require any arguments or inputs.
Syntax: =TODAY()Example: If you enter the formula “=TODAY()” in a cell, it will display the current date whenever the worksheet is recalculated. The value returned by the TODAY function will update automatically each day.
NOW function:
The NOW function returns the current date and time as a serial number in Excel’s date and time format. Similar to the TODAY function, it does not require any arguments.
Syntax: =NOW()Example: If you enter the formula “=NOW()” in a cell, it will display the current date and time. The value returned by the NOW function will continuously update whenever the worksheet is recalculated, reflecting the current date and time.
Both the TODAY and NOW functions are volatile functions, which means they recalculate whenever there is any change in the worksheet. Therefore, their values are always up-to-date based on the current system date and time.
These functions can be combined with other Excel functions to perform various calculations and comparisons based on the current date or time. For example, you can use them to calculate the number of days remaining until a deadline, determine the age based on the current date and birthdate, or trigger conditional formatting based on specific time intervals.
Note: The TODAY and NOW functions retrieve the date and time based on your computer’s system clock. If you want to capture the date and time from a specific time zone or server, you may need to use other functions or formulas, such as connecting to external data sources or utilizing macros in VBA.
CONCATENATE function
The CONCATENATE function in Excel is used to combine (concatenate) multiple text strings into a single string. It is useful for merging data from different cells or adding additional text to create a customized text output. The CONCATENATE function allows you to join text strings together without any separator. In Excel 2016 and later versions, the CONCATENATE function has been replaced by the CONCAT function, which offers the same functionality with an improved syntax. Here’s an explanation of the CONCATENATE function:
Syntax: =CONCATENATE(text1, text2, ...)The CONCATENATE function takes multiple text strings as arguments and joins them together in the order they are specified.
Example: Suppose you have the following data in cells A1, B1, and C1: “Hello,” “world!”, and ” How are you?” respectively. You want to combine these text strings into a single sentence. You can use the CONCATENATE function as follows:
=CONCATENATE(A1, B1, C1)The formula will concatenate the text strings from cells A1, B1, and C1 and produce the output “Hello, world! How are you?” in the cell where the formula is entered.
Alternatively, you can use the CONCAT function in Excel 2016 and later versions:
=CONCAT(A1, B1, C1)The CONCAT function works the same way as the CONCATENATE function but offers a more streamlined syntax.
You can also include additional text within the CONCATENATE function by using double quotation marks. For example:
=CONCATENATE("The result is: ", A1)This formula will concatenate the text “The result is: ” with the value in cell A1.
IFERROR function
The IFERROR function in Excel is used to handle and manage errors that may occur in formulas. It allows you to specify a value or action to be taken if a formula generates an error, helping to prevent error messages and unexpected results in your calculations. The IFERROR function checks whether a formula results in an error and returns a specified value if an error is detected. Here’s an explanation of the IFERROR function:
Syntax: =IFERROR(value, value_if_error)- value: This is the expression or formula that you want to evaluate for errors.
- value_if_error: This is the value or action that you want to display or perform if the formula specified in the value parameter generates an error.
Example: Suppose you have a division formula that calculates the ratio of two numbers in cell A1 and B1. However, there may be instances where the denominator (B1) is zero, resulting in a division by zero error. To handle this error and display a specific message or value, you can use the IFERROR function:
=IFERROR(A1/B1, "Error: Division by Zero")In this example, the formula attempts to perform the division A1/B1. If the denominator (B1) is zero, the division will result in an error. The IFERROR function detects the error and returns the value “Error: Division by Zero” instead of the error message.
You can also use the IFERROR function to perform alternative calculations or actions when an error occurs. For example:
=IFERROR(A1/B1, C1*D1)In this case, if the division A1/B1 generates an error, the formula will instead perform the calculation C1*D1 and return the result.
MONTH and YEAR functions
The MONTH and YEAR functions in Excel are used to extract the month and year from a given date, respectively. These functions are particularly useful when you want to analyze or categorize data based on specific months or years. Here’s an explanation of each function:
MONTH function:
The MONTH function returns the month number from a given date. The result is a number between 1 and 12, representing the month of the date.
Syntax: =MONTH(serial_number)- serial_number: This is the date from which you want to extract the month. It can be a date value, a reference to a cell containing a date, or a formula that returns a valid date.
Example: If cell A1 contains the date “12/25/2023”, you can use the MONTH function to extract the month number as follows:
=MONTH(A1)The formula will return the value 12, representing the month of December.
YEAR function:
The YEAR function returns the year from a given date. The result is a four-digit number representing the year.
Syntax: =YEAR(serial_number)- serial_number: This is the date from which you want to extract the year. It can be a date value, a reference to a cell containing a date, or a formula that returns a valid date.
Example: If cell A1 contains the date “12/25/2023”, you can use the YEAR function to extract the year as follows:
=YEAR(A1)The formula will return the value 2023, representing the year.
The MONTH and YEAR functions are often used in combination with other Excel functions to perform various calculations and analyses. For example, you can use them to summarize sales data by month, identify trends over different years, or filter data based on specific months or years.
It’s important to note that the MONTH and YEAR functions extract the month and year as numeric values. If you want to display the month or year as a text string (e.g., “January” or “2023”), you can use additional functions like TEXT or CONCATENATE to format the results accordingly.
TRIM function
The TRIM function in Excel is used to remove extra leading, trailing, and excessive spaces between words within a text string. It is particularly useful when working with data that may contain unnecessary spaces that can affect the accuracy of calculations or cause issues when comparing values. The TRIM function helps clean up and normalize text data by eliminating unwanted spaces. Here’s an explanation of the TRIM function:
Syntax: =TRIM(text)- text: This is the text string or cell reference containing the text you want to trim. It can be a single cell, a range of cells, or a text string enclosed in double quotation marks.
Example: Suppose you have a cell (A1) with the text string ” Hello, world! ” which contains leading and trailing spaces, as well as excessive spaces between the words. You can use the TRIM function to remove these extra spaces as follows:
=TRIM(A1)The formula will return the trimmed text string without any leading or trailing spaces, and with only a single space between the words: “Hello, world!”
The TRIM function is often used in combination with other functions that manipulate text or perform string comparisons. It helps ensure consistency and accuracy when working with text data by removing unintended spaces that can affect the results of calculations, sorting, or searching.
It’s important to note that the TRIM function only removes spaces, including regular spaces (ASCII character 32), but not other non-printable characters or special characters. If you need to remove other characters from a text string, you may need to use additional functions like SUBSTITUTE or CLEAN.
FIND function
The FIND function in Excel is used to locate the position of a specific character or substring within a text string. It helps you determine the starting position of a character or substring, which can be useful for extracting or manipulating text data. The FIND function is case-sensitive, meaning it distinguishes between uppercase and lowercase characters. Here’s an explanation of the FIND function:
Syntax: =FIND(find_text, within_text, [start_num])- find_text: This is the character or substring that you want to find within the text string. It can be a single character, a word, or a combination of characters.
- within_text: This is the text string in which you want to search for the find_text. It can be a cell reference or a text string enclosed in double quotation marks.
- start_num: (Optional) This parameter specifies the position within the within_text where the search should begin. If omitted, the search starts from the first character. You can use this parameter to skip a certain number of characters before starting the search.
Example: Suppose you have the text string “Excel functions are powerful” in cell A1. You want to find the position of the word “functions” within this text string. You can use the FIND function as follows:
=FIND("functions", A1)The formula will return the value 7 because “functions” starts at the 7th position within the text string.
The FIND function is often used in combination with other functions to extract or manipulate specific parts of a text string. For example, you can use it with the MID function to extract a substring starting from a certain position.
It’s important to note that the FIND function returns the position of the first occurrence of the find_text within the within_text. If the find_text is not found, the function returns the #VALUE! error.
If you want to perform a case-insensitive search, you can use the SEARCH function instead, which works in a similar way to the FIND function but ignores case.
DATE function
The DATE function in Excel is used to create a date value based on specified year, month, and day components. It allows you to generate dates that can be used in various calculations, comparisons, and data analysis. The DATE function takes individual arguments for year, month, and day and returns a date value. Here’s an explanation of the DATE function:
Syntax: =DATE(year, month, day)- year: This is the numerical value representing the year. It can be entered as a four-digit number (e.g., 2023) or as a reference to a cell containing a valid year.
- month: This is the numerical value representing the month. It can be entered as a number between 1 and 12, or as a reference to a cell containing a valid month number.
- day: This is the numerical value representing the day. It can be entered as a number between 1 and 31, or as a reference to a cell containing a valid day number.
Example: To create a date value for January 1, 2023, you can use the DATE function as follows:
=DATE(2023, 1, 1)The formula will return the date value corresponding to January 1, 2023.
You can also use cell references for the arguments to create dynamic date values based on other values in your worksheet. For example, if cell A1 contains the year, cell A2 contains the month, and cell A3 contains the day, you can use the following formula:
=DATE(A1, A2, A3)This formula will generate a date value based on the year, month, and day specified in cells A1, A2, and A3, respectively.
The DATE function is often used in combination with other functions that require date values, such as calculating the difference between two dates, determining the day of the week, or performing date-based comparisons.
Super Advanced Excel Formulas
Here are a few super-advanced Excel formulas that can help you perform complex calculations and data analysis
XLOOKUP function
The XLOOKUP formula is a powerful and versatile function introduced in Excel 365 that allows you to perform advanced lookup operations. It is an alternative to traditional lookup functions like VLOOKUP and HLOOKUP, offering more flexibility and capabilities. The XLOOKUP function can search for a value in a range and return a corresponding value from another column or range. Here’s an explanation of the XLOOKUP formula:
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])lookup_value: This is the value you want to search for within the lookup_array.lookup_array: This is the range or array where you want to search for the lookup_value.return_array: This is the range or array from which you want to retrieve the corresponding value.if_not_found: (Optional) This parameter specifies the value to return if the lookup_value is not found. It can be a specific value, an error value, or a reference to a cell.match_mode: (Optional) This parameter controls how exact or approximate matches are handled. You can specify values like 0 for an exact match, -1 for a range lookup, or 1 for an approximate match.search_mode: (Optional) This parameter determines the direction of the search. You can use 1 for a search from top to bottom or -1 for a search from bottom to top.
Example: Suppose you have a list of employee names in column A and their corresponding salaries in column B. You want to find the salary of an employee named “John” using the XLOOKUP function. You can use the following formula:
=XLOOKUP("John", A:A, B:B)The formula will search for the name “John” in column A and return the corresponding salary from column B.
The XLOOKUP function offers several advantages over traditional lookup functions. It allows you to perform left-to-right lookups, search for values in multiple columns or arrays, handle errors more effectively, and perform approximate matches. Additionally, it simplifies complex lookup scenarios by providing a single function that can handle various lookup requirements.
It’s important to note that the XLOOKUP function is available in Excel 365 and later versions. If you are using an older version of Excel, you may not have access to this function. In that case, you can consider using alternative lookup functions like VLOOKUP or INDEX-MATCH.
SUMPRODUCT formula
The SUMPRODUCT formula in Excel is a versatile function that allows you to multiply corresponding elements in arrays and then sum the products. It is commonly used for advanced data analysis, such as calculating weighted averages, performing conditional summing, and conducting array calculations. Here’s an explanation of the SUMPRODUCT formula:
Syntax: =SUMPRODUCT(array1, array2, ...)array1, array2, ...: These are the arrays or ranges that you want to multiply and then sum the products. You can specify multiple arrays separated by commas.
Example 1 – Basic Usage: Suppose you have two arrays, A1:A5 and B1:B5, containing numeric values. You want to calculate the sum of the products of corresponding elements in the arrays. You can use the SUMPRODUCT formula as follows:
=SUMPRODUCT(A1:A5, B1:B5)The formula will multiply each element in array A1:A5 with the corresponding element in array B1:B5 and then sum the resulting products.
Example 2 – Weighted Average: Let’s say you have a list of values in column A and corresponding weights in column B. You want to calculate the weighted average of the values based on the weights. You can use the SUMPRODUCT formula to achieve this:
=SUMPRODUCT(A1:A5, B1:B5) / SUM(B1:B5)The formula multiplies each value in column A with its corresponding weight in column B, sums the products, and then divides the sum by the total sum of the weights in column B.
The SUMPRODUCT formula is particularly useful when working with multiple arrays and performing calculations that involve weighting, conditions, or complex arrays. It allows you to perform calculations across arrays without the need for complex array formulas or iterative calculations.
Additionally, you can combine the SUMPRODUCT formula with other functions, such as SUMIF or COUNTIF, to perform more advanced calculations that involve conditions or criteria.
SUMIFS function
The SUMIFS function in Excel is used to sum values in a range based on multiple criteria or conditions. It allows you to perform conditional summing, where you can specify one or more criteria that the data must meet in order to be included in the sum calculation. Here’s an explanation of the SUMIFS function:
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)sum_range: This is the range of cells that you want to sum based on the specified criteria.criteria_range1, criteria_range2, ...: These are the ranges of cells where you want to apply the criteria. The criteria are compared against the values in these ranges.criteria1, criteria2, ...: These are the conditions or criteria that the data must meet in order to be included in the sum calculation. You can specify multiple criteria by using additional pairs of criteria_range and criteria arguments.
Example: Suppose you have a table with sales data, where column A contains the salesperson names, column B contains the sales amounts, and column C contains the region names. You want to calculate the sum of sales amounts for the salesperson “John” in the “West” region. You can use the SUMIFS formula as follows:
=SUMIFS(B:B, A:A, "John", C:C, "West")The formula will sum the values in column B (sales amounts) that meet the criteria of having “John” in column A (salesperson names) and “West” in column C (region names).
The SUMIFS function allows you to specify multiple criteria for different columns or ranges. You can use logical operators like “>” (greater than), “<” (less than), “<>” (not equal to), and wildcards like “*” and “?” in the criteria to make the conditions more flexible.
IFS function
The IFS function in Excel is a versatile logical function that allows you to test multiple conditions and return a value based on the first condition that evaluates to TRUE. It simplifies complex nested IF statements by providing a more streamlined and readable way to handle multiple conditions. Here’s an explanation of the IFS function:
Syntax: =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)logical_test1, logical_test2, ...: These are the logical expressions or conditions that you want to evaluate. Each logical_test is separated by a comma.value_if_true1, value_if_true2, ...: These are the values or results to be returned if the corresponding logical_test evaluates to TRUE.
Example: Suppose you have a list of students’ scores in column A, and you want to assign grades based on the following criteria:
- If the score is greater than or equal to 90, assign the grade “A”.
- If the score is greater than or equal to 80, assign the grade “B”.
- If the score is greater than or equal to 70, assign the grade “C”.
- If the score is greater than or equal to 60, assign the grade “D”.
- Otherwise, assign the grade “F”.
You can use the IFS formula as follows:
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", TRUE, "F")The formula will evaluate each condition in order and return the corresponding grade based on the first condition that evaluates to TRUE. If none of the conditions are met, the formula will return “F”.
The IFS function allows you to handle a large number of conditions in a more concise and readable manner. You can include as many logical_test and value_if_true pairs as needed, making it suitable for complex decision-making scenarios.
It’s important to note that the IFS function requires at least one logical_test and value_if_true pair. Also, if none of the conditions are met, you should include a final TRUE condition to provide a default value or result.
The IFS function is available in Excel 2019, Excel for Microsoft 365, and Excel Online. If you are using an older version of Excel, you can achieve similar functionality by nesting multiple IF functions.
FILTER function
The FILTER function in Excel is a powerful array function that allows you to extract data from a range or array based on specified criteria. It filters the data and returns a new range or array that meets the specified conditions. The FILTER function is particularly useful for advanced data analysis and dynamic reporting. Here’s an explanation of the FILTER function:
Syntax: =FILTER(array, include, [if_empty])array: This is the range or array of data that you want to filter.include: This is the array or range of criteria or conditions that the data must meet in order to be included in the filtered result. It should have the same number of rows or elements as the array.if_empty: (Optional) This parameter specifies the value or result to return if no data matches the specified criteria. It can be a specific value, an error value, or a reference to a cell.
Example: Suppose you have a table of sales data, where column A contains the product names, column B contains the sales amounts, and column C contains the regions. You want to filter the data and extract the sales amounts for products sold in the “West” region. You can use the FILTER function as follows:
=FILTER(B:B, C:C = "West")The formula will filter the sales amounts in column B based on the criteria that the corresponding region in column C is “West”. It will return a new array with the filtered sales amounts.
The FILTER function can handle multiple conditions by combining logical operators like AND and OR. For example, if you want to filter the sales amounts for products sold in the “West” region with sales greater than $1000, you can use the following formula:
=FILTER(B:B, (C:C = "West") * (B:B > 1000))The formula uses the * operator to represent the logical AND operation between the two conditions.
The FILTER function is an array function, so it works with arrays and can return multiple values. You can use it in combination with other functions to perform further calculations or analysis on the filtered data.
NETWORKDAYS.INTL function
The NETWORKDAYS.INTL function in Excel is an advanced version of the NETWORKDAYS function that allows you to calculate the number of working days between two dates, excluding weekends and specified non-working days based on a custom-defined workweek. This function is useful when you have a work schedule that differs from the traditional Monday-to-Friday workweek. Here’s an explanation of the NETWORKDAYS.INTL function:
Syntax: =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])start_date:This is the starting date from which you want to calculate the number of working days.end_date: This is the ending date up to which you want to calculate the number of working days.weekend: (Optional) This parameter allows you to specify the days of the week that should be considered as weekends and not included in the calculation. It can be a number or a string of numbers representing the days of the week. For example, “1” represents Monday, “2” represents Tuesday, and so on. You can also use a string of numbers to specify multiple weekend days, such as “13” for Saturday and Sunday.holidays: (Optional) This parameter allows you to specify additional non-working days, such as holidays or other specific days, that should be excluded from the calculation. It can be a range or an array of holiday dates.
Example: Suppose you have a work schedule that includes weekends (Saturday and Sunday) and a specific holiday on January 1, 2023. You want to calculate the number of working days between December 25, 2022, and January 10, 2023, excluding weekends and the holiday. You can use the NETWORKDAYS.INTL function as follows:
=NETWORKDAYS.INTL(DATE(2022, 12, 25), DATE(2023, 1, 10), "0000001", DATE(2023, 1, 1))In this example, the formula calculates the number of working days between the specified start_date and end_date, excluding Saturdays and Sundays (specified as “0000001”) and the holiday on January 1, 202
