Search results

At the core, this formula uses the SEARCH function to look for multiple strings inside a cell. Inside the left SUMPRODUCT, SEARCH looks for all strings in the named range "include".

In the right SUMPRODUCT, SEARCH looks for all strings in the named range "exclude".

In both...Read more

The goal is to "flag" records that are neither "Red" nor "Green". More specifically, we want to check the color in column B, and leave an "x" in rows where the color is NOT "Red" OR "Green". If the color is "Red" OR "Green", we want to display nothing.

IF function...Read more

The table contains basic order information, with columns for Date, Product, Name, and Amount. The Helper column is used to create a special lookup value, as explained below. The goal is to retrieve the nth matching record in a table for a specific product, which is entered in cell I4. For...Read more

The goal is to retrieve the nth matching record in a table when targeting a specific product. For example, if the value in cell H4 is "A", the formula in H7 should return the name "John", since this is the first name in the table associated with product "A". In the same way, the...Read more

The goal is to list the working days between a start date and an end date. In the simplest form, this means we want to list dates that are Monday, Tuesday, Wednesday, Thursday, or Friday, but exclude dates that are Saturday or Sunday. In addition, we need an option to exclude a list of...Read more

The core of this formula is based on another formula that calculates the "current row" in a range:

=ROW()-ROW(rng.firstcell)+1

In brief, we get the current row in the workbook, then subtract the...Read more

In this example, the goal is to highlight dates that occur on weekends. In other words, we want to highlight dates that land on either Saturday or Sunday. This problem can be easily solved by applying conditional formatting with a formula based on the WEEKDAY function together with the OR...Read more

In the example shown, the goal is to add row numbers in column B only when there is a value in column C. The formula in B5 is:

=IF(ISBLANK(C5),"",COUNTA($C$5:C5))

The IF function first checks if cell C5...Read more

In this example, the goal is to calculate a weighted average of scores for each name in the table using the weights that appear in the named range weights (I5:K5) and the scores in columns C through E. A weighted average (also called a weighted mean)...Read more

The goal is to calculate a tax amount with both fixed and variable components according to the following logic:

1. If the amount is less than $1000, only the base tax applies.
2. If the amount is $1000 or greater, the result is the base tax + 15% * the amount over $
...Read more

Dynamic ranges are also known as expanding ranges because they automatically expand and contract to accommodate new or deleted data. You can see a video demo of this approach here. This formula uses the OFFSET function to...Read more

In this example, the goal is to create a formula that will return "Pass" or "Fail" depending on whether a student has a passing score in at least 4 out of 6 subjects. This problem can be easily solved with a formula based on the COUNTIF function...Read more

Ever needed to calculate someone's retirement date? Or figure out when a 30-year mortgage will end? Or maybe you're setting a contract expiration date? In each case, you need a way to add years to a date.

Here's something that might surprise...Read more

FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars. The LEFT function then extracts characters starting at the left side of the text, up to (position - 1...Read more

The goal is to look up and retrieve employee information in a table that contains unique id values in the first column. The VLOOKUP function is straightforward to use with data in this format, but you can easily use the XLOOKUP function as well. See below for a detailed explanation of both...Read more

This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove any blank values from the data:

FILTER(B5:B16,B5:B16<>"")

The <> symbol is a...Read more

When conditional formatting is applied with a formula, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the active cell when the rule is created is assumed to be cell E5, with the range E5:E14 selected.

As the formula is...Read more

This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove limit data to values associated with group A only:

FILTER(B5:B16,C5:C16=E4)

Notice we are...Read more

In this example, we need to construct logic that filters data to include:

account begins with "x" AND region is "east", and month is NOT April.

The filtering logic of this formula (the include argument) is created by chaining together three expressions that use...Read more

The goal of this example is to count rows in the data where the date joined falls between start and end dates (inclusive) and the age also falls into the age ranges seen in column G. The formula is complicated somewhat by the fact that the age range labels are actually text, so we need to...Read more

The goal is to calculate a tax of 6% on amounts up to 20,000 and a tax of 10% on amounts of 20,000 or greater. This problem illustrates how to use the IF function to return different calculations. At the core, this formula uses a single IF function. The...Read more

The goal is to return the first non-blank value in each row from columns B:E, moving left to right. One way to solve this problem is with a series of nested IF statements. Since all cells are contiguous (connected) another way to get the first value is with the XLOOKUP function. Both approaches...Read more

This example uses the UNIQUE function together with the FILTER function. The FILTER function removes data that does not meet required criteria, and the UNIQUE function further limits results to unique values only.

Working from the inside out, the FILTER function is used to collect source...Read more