Formulas

To calculate the longest winning streak in Excel, you can use a formula based on the SCAN function with the LAMBDA and MAX functions.

To list the most frequently occurring numbers in a column, use a formula based on the LET, UNIQUE, SORT, HSTACK, and COUNTIF functions.

To list the most common text values in a column or a row, you can use a formula using the LET, UNIQUE, SORT, HSTACK, and COUNTIF functions.

To count rows that meet two or more specified criteria, you can use a formula based on the versatile COUNTIFS function.

To count matches between two columns in Excel based on the corresponding rows, you can use the SUMPRODUCT function.

To get the last match in a range where cell contains a matching value you can use the XLOOKUP, TRUE, ISNUMBER and SEARCH function.

To match the first error in a range in Excel, you can apply a formula based on the XMATCH, TRUE, and ISERROR functions.

To count cells that contain errors, you can use the SUMPRODUCT and ISERROR functions with the double negative method.

Learn how to calculate hours between two times in Excel using an IF formula with conditional logic to manage different scenarios.

This tutorial shows how to create an IF formula with Boolean logic to sum values based on multiple conditions.

To filter and extract every nth row in Excel, you can combine the FILTER function with the MOD, ROW, and SEQUENCE functions.

To filter data and extract records between two dates, you can use a formula based on the FILTER function with Boolean logic.

To filter data with a case-sensitive method in Excel, you can use a formula based on the FILTER and the EXACT function.

To compare two delimited text strings and extract common values, you can use the TEXTJOIN, TRUE, FILTER, and COUNTIF functions.

To extract unique values from multiple ranges, you can apply a formula that uses the UNIQUE function with the VSTACK function.

To combine or consolidate ranges in Excel, you can use a formula based on the VSTACK function or the HSTACK function.

To combine data in multiple worksheets, you can use a formula based on the VSTACK, LET, FILTER, and CHOOSECOLS functions.

To sum the first n matching values in a range in Excel, you can use a formula based on the SUM, TAKE, and FILTER functions.

To sum the bottom n values in a range in Excel, you can use a formula based on the SUM and SMALL functions.

Learn how to sum every nth column (for example, every second, third, or fifth column) using the Excel SUM, FILTER, and SEQUENCE functions.

Learn how to sum every n rows in Excel using regular functions like SUM and OFFSET. Alternatively, you can apply user-defined functions.

To extract the first word from some text in Excel, you can use a formula with built-in functions like FIND and LEFT.

Learn how to remove file extensions from a filename using custom Excel functions. Also, use the LEFT and FIND functions to clean data.

Learn how to delete all text before a number in Excel using the TEXTJOIN, MID, TRUE, IF, ISNUMBER, LEN, and INDIRECT functions.

To count numbers where the nth digit is a given number in Excel, use the SUMPRODUCT function or the RIGHT, MID, and COUNTIF functions.

Learn how to use the MEDIAN IF formula in Excel to identify the middle number of values that meet one or more specific criteria.

The XLFN prefix in front of a formula indicates that the formula uses a function not available in your current version of Excel.

Learn how to create a case-sensitive exact match formula by combining the XLOOKUP and EXACT functions to retrieve data based on text matching.