Search results

This formula relies on the FILTER function to retrieve data based on a logical test built with the COUNTIF function:

=FILTER(list1,COUNTIF(list2,list1))

working from the inside out, the...Read more

A common situation in Excel is to use the SORT function to sort results returned by the FILTER function. However, a formula based on the FILTER and SORT may return an error when no data is returned. In this...Read more

In this example, the goal is to list and count values that are duplicated in a set of data at least n times, where n is provided as a variable in cell D5. All data is in the named range data (B5:B16). In the...Read more

In this example, the goal is to display the biggest 3 gainers and losers in a set of data where Start and End columns contain values at two points in time, and Change contains the percentage change in the values. The data in B5:E16 is defined as an Excel Table...Read more

In this example, the goal is to calculate a sum for any given group ("A", "B", or "C") across all three months of data in the range C5:E16. In other words, we want to perform a "sum if" with a data range that contains three columns. For convenience only, data (C5:E16) and...Read more

The goal is to retrieve the nth matching record in a set of data, after filtering on a specific product. In the worksheet shown, the product in H4 and the value for n in H5 are inputs that can be changed at any time. For instance, if the product in H4 is "A" and the value in H5...Read more

In this example, the goal is to look up and retrieve all names for a given team and return them in a single cell as a comma‑separated list. At the core, this is a lookup problem, but the twist is that we want to return multiple matches for each team, not just one. That means...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 generate a list of "nth weekdays of the month" with a formula. For example, the formula should be able to create a list of any of the following:

• 2nd Tuesdays of the month
• 1st Fridays of the month
...Read more

In this example, the goal is to sum values in matching columns and rows. Specifically, we want to sum values in data (C5:G14) where the column code is "A" and the day is "Wed". One way to solve this problem is with the SUMPRODUCT function...Read more

At the core, this formula relies on the SUMPRODUCT function to sum values in matching columns in the named range data C5:G14. If all data were provided to SUMPRODUCT in a single range, the result would be the sum of all values in the range:

...Read more

In this example, the goal is to retrieve the top 3 scores in column D that appear in a given group, entered as a variable in cell F5. If the group is changed, the formulas should calculate new results. The core of the solution is the LARGE function, which can be used to retrieve the "nth"...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 get the maximum value for each group in the data as shown. The easiest way to solve this problem is with the MAXIFS function. However, there are actually several options. If you need more flexibility (i.e. you need to work with arrays and not ranges), you can...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

The goal is to find the longest text string in the range B5:B16. At the core, this is a lookup problem that requires creating a value (the string length) that does not exist in the data as part of the formula. The easiest way to solve this problem is with the ...Read more

In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. However, the result needs to ignore any zeros that appear in the data. This formula can be easily solved with the AVERAGEIF function...Read more

In this example, the goal is to get the minimum value for each group in the data as shown. The easiest way to solve this problem is with the MINIFS function. However, there are actually several options. If you need more flexibility (you need to work with arrays instead of ranges), you can...Read more