Search results

In this example, the goal is to sum the amounts in the table using the "Start" and "End" values in columns F and G. For convenience, all data is in an Excel Table called data, which means we can use the...Read more

In this example the goal is to return rows in the range B5:E15 that have a specific state value in column E. To make the example dynamic, the state is a variable entered in cell H4. When the state in H4 is changed, the formula should return a new set of records. This is a perfect...Read more

In this example, the goal is to sum the amounts in column C by quarter in column G. Column D is a helper column, and the formula to calculate quarters from the dates in column B is explained below. All data is in an Excel...Read more

In this example, the goal is to calculate a count for the longest winning streak in a set of data. In the worksheet shown, wins ("W") and losses ("L") are recorded in column C, so this means we want to count the longest consecutive series of W's. Although we are specifically counting the...Read more

The goal is to filter the horizontal data in the range C4:L6 to extract members of the group "fox" and display results with data transposed to a vertical format. For convenience and readability, we have two named ranges to work with: data (C4:L6) and group...Read more

This formula relies on a technique called "nested IFs" to handle a series of options and results. With nested IFs, one IF function is nested inside another, a process that is explained in some detail here.

The formula in this example is purposely more...Read more

In this example, the goal is to return a count for each color that appears in column C, using the color values already in column E as criteria. When working with data, a common need is to perform summary calculations that show total counts in different ways. For example, total counts by...Read more

The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references. Technically, the space character is the "range intersect" operator and the #NULL! error is reporting that the...Read more

This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and "flood" the text with spaces where we find one. Then we come back and grab text from the right.

Working from the inside out, we use the SUBSTITUTE function to find all...Read more

In this example, the goal is to count unique values that meet one or more specific conditions. In the example shown, the formula used in cell H7 is:

=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))

At the core, this formula...Read more

In this example, the goal is to list values in a given group that are within a given tolerance. The group is set in cell G4, and the target value is entered in cell G5. The allowed tolerance is entered in cell G6. The data comes from an Excel Table called...Read more

In this example, the goal is to calculate a total quantity for each color across the two ranges shown in the worksheet. The two ranges are "non-contiguous", which means they are not connected or touching. Both ranges contain a list of colors in the first column and quantities in the second...Read more

In this example, the goal is to highlight differences in two ranges, B2:B11 and C2:C11, using conditional formatting. To do this, we need to create a new conditional formatting rule, triggered by a formula, like this:

1. Select the range B2:C11, starting at cell B2.
...Read more

In this example, the goal is to return a random group ("A", "B", or "C") at each new row. The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function. In the current version of Excel, it is also possible to generate all random groups in one step with the RANDARRAY...Read more

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

If you are new to the SUMIFS function, you can find a basic overview with many examples here.

The SUMIFS function is designed to sum numeric values based on one or more criteria. In specific cases however, you may be able to use SUMIFS to "look up...Read more

This formula relies on the FILTER function to retrieve data based on a logical test built with simple expressions and boolean logic:

(D5:D14="red")+(D5:D14="blue")
...Read more

In this example, the goal is to get the last value in column B, even when data may contain empty cells. A secondary goal is to get the corresponding value in column C. This is useful for analyzing datasets where the most recent or last entry is significant. In the current version of...Read more

This formula relies on a specific behavior of INDEX — although it seems that INDEX returns the value at a particular location, it actually returns a reference to the location. In most formulas, you wouldn't notice the difference – Excel simply evaluates the reference and...Read more

In this example, the goal is to get the maximum value in the data for each month listed in column E. The easiest way to do this is with the MAXIFS function, which is designed to return a maximum value based on one or more criteria. In older versions of Excel without the MAXIFS function, you...Read more

In the example shown, we have a set of order data that includes Date, Product, Name, and Amount. The data is sorted by date in ascending order. The goal is to look up the latest order for a given person by Name. In other words, we want the last match by name. The challenge is that Excel...Read more

In this example, we want to apply three different colors, depending on how much the original date varies from the current date:

1. Green if the variance is less than 3 days
2. Yellow if the variance is between 3 and 10 days
3. Red if the variance is greater than 10
...Read more