Search results

Working from the inside out, the formula first joins the values the 5 cells to the left using the concatenation operator (...Read more

In this example, the goal is to filter data based on multiple criteria with the FILTER function. Specifically, we want to select data where (1) the group = "A" and (2) the Score is greater than 80. At first glance, it's not obvious how to do this with the FILTER function. Unlike older...Read more

Note: in the current version of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER...Read more

At the core, this formula uses the WEEKDAY function to test a number of dates to see if they land on a given day of week (dow) and the SUMPRODUCT function to tally up the total.

When given a date,...Read more

In this example, the goal is to create a running count for a specific value that appears in column B. The value to count is entered in cell E5, which is the named range value. The core of the solution explained below is the...Read more

In this example, the goal is to sum the Amounts in C5:C16 when the Lead in D5:D16 is not blank (i.e., not empty). A good way to solve this problem is to use the SUMIFS function. However, you can also use the ...Read more

This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE function.

The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range for the first argument (array...Read more

In the example shown, we have a list of amounts by month. The goal is to dynamically sum values through a given number of months using a variable n in cell E5. Since month names are just text, and months are listed in order, the key requirement is to sum amounts...Read more

Although FILTER is commonly used to filter rows, you can also filter columns. The trick is to use an include argument that operates on columns instead of rows. In this example, we use a hard-coded array constant to filter out unwanted columns, but...Read more

In this example, the goal is to retrieve employee information from a table using only a partial match on the last name. In other words, by typing "Aya" into cell H4, the formula should retrieve information about Michael Ayala.

The VLOOKUP function...Read more

At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hard-coded as array constant:

MID(B5,{10,9,8,7,6,5,4,3,2,1},1)
...Read more

In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the "Months" column. The "Replaced" column shows the date equipment was...Read more

This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE and IF functions. The result is the top 3 scores in group B.

The FILTER function applies criteria with...Read more

The gist of this problem is that we want to get the first non-blank cell, but we don't have a direct way to do that in Excel. The easiest way to solve this problem is with the XLOOKUP function.

XLOOKUP function

The XLOOKUP...Read more

In this example, the goal is to generate a list of people who were invited but did not attend an unspecified event. More specifically, we need to compare the names in B5:B16 against the names in D5:D12 and return the missing names. For convenience, list1 (B5:B16) and...Read more

In this example, the goal is to count test scores in column C that are greater than 90. The simplest way to do this is with the COUNTIF function, which takes two arguments, range and criteria...Read more

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D15, which contains all of the data without headers. The include argument is...Read more

Starting from the inside out, the MID function is used to extract all text after "@":

MID(B5,FIND("@",B5),LEN(B5))

The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text....Read more

This example is set up in two parts for clarity: (1) a formula to determine the top 3 amounts for each month and (2) a formula to retrieve the client name for each of the top 3 monthly amounts.

Note there is no actual rank in the source data. Instead, we are using the LARGE function to...Read more

The goal is to mark rows where the color is "Red" AND the size is "Small" or "Medium". To perform this task, you can use the IF function in combination with the AND function and the...Read more

This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is:

type,G$4

where type is the named range D5:D14...Read more

The CELL function can provide a wide range of information about cell properties. One property is called "protect" and indicates whether a cell is unlocked or locked. All cells start out "locked" in a new Excel workbook, but this setting has no effect until a worksheet is protected.

The...Read more