Search results

In this example, the goal is to subtotal (count and sum) values based on cell color. This is a tricky problem, because there is no Excel function that will let you count cells by color directly. There are several different approaches, as explained below.

Standard formula logic

...Read more

In this example, the goal is to retrieve the close price on July 1, 2021 for each symbol shown in column B. This can be done with the STOCKHISTORY function, whose main purpose is to retrieve historical information for a...Read more

In this example, the goal is to calculate a weekly total using the data as shown. Notice each week corresponds to 5 rows of data (Monday-Friday) so we will need to sum values in every 5 rows. To build a range that corresponds to the correct 5 rows for each week, we use the OFFSET...Read more

The goal is to extract records with dates that are greater than or equal to a start date in F5 and less than or equal to an end date in G5. You might think we can use the AND function inside FILTER to solve this problem. However, because AND returns just a...Read more

In this example, the goal is to generate a list of quarter start and quarter end dates. This can be done by combining the SEQUENCE function with the EDATE and EOMONTH functions, as explained below.

The SEQUENCE function

The SEQUENCE...Read more

Note: this formula is the set-up for a formula that can extract and display data using a predefined sort order in a helper column. One example here.

The core of this formula is the RANK function, which is used to...Read more

In this example, the goal is to copy every nth value from column B, where n is a variable that can be changed as needed.

In Excel, it's difficult to create formulas that skip rows following a certain pattern, because the references in the formula will automatically change as the formula...Read more

In this example, the goal is to retrieve historical stock price information for a given stock, provided as a ticker symbol like "MSFT", "AAPL", "MMM", etc.  over the past n days, where n is a variable that can be changed as desired. In addition, the data should be...Read more

In this example, the goal is to sum numeric values by month while ignoring the year that contains the date. The solution below is based on the SUMPRODUCT function, the MONTH function, and Boolean algebra. For convenience, amount...Read more

In this example, the goal is to extract the top 3 quiz scores for each name from the 5 scores that appear in columns C, D, E, F, and G. In other words, for each name listed, we want the best score, the 2nd best score, and the 3rd best score. This problem can be solved with the LARGE function....Read more

In this example, the goal is to count "z" or "c" values in the named range data, but only when the column header is "A" or "B". The formula used to perform this calculation is based on the SUMPRODUCT...Read more

In this example, the goal is to create an "locked" reference that won't change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation.

The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not...Read more

In this example, the goal is to get monthly currency exchange rates for a given currency pair (i.e. USD > EUR, USD > GBP, CAD > JPY, etc.). Currency abbreviations are entered in cells F5 and F6, and the start and end dates are entered in cells F7 and F8. If any of these four inputs are...Read more

Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use Boolean logic to create a lookup array on the fly composed...Read more

In this example, the goal is to retrieve information about the lowest three estimates in the data shown. The problem is that there are some duplicate values in the estimate column. This means we will have some trouble trying to display the names of the 2nd and 3rd lowest suppliers because the...Read more