Customer is new
This formula uses an expanding range for the criteria range inside COUNTIFS:
COUNTIFS($B$5:B5,B5)
Because the first reference is absolute and the second reference is relative, the range expands...Read more
Dave Bruns
Left lookup with VLOOKUP
One of the VLOOKUP function's key limitations is that it can only look up values to the right. In other words, the column that contains lookup values must sit to the left of the values you want to retrieve with VLOOKUP. There is no way to override this...Read more
Average if not blank
In this example, the goal is to average the Prices in C5:C16 when the Group in D5:D16 is not blank (i.e. not empty). The traditional way to solve this problem is to use the AVERAGEIFS function. However, you can also use the ...Read more
Sum time by week and project
In this example, the sum range is the named range "time", entered as an Excel time in hh:mm format. The first criteria inside SUMIFS includes dates that are greater than or equal to week date in column F:
date,">="...Read moreHow to fix a circular reference error
A circular reference occurs when a formula refers directly to its own cell, or refers to another cell that depends on the original cell. This creates an infinite loop that cannot be resolved. For example, if cell A1 contains a formula that refers to B1, and B1 contains a formula that refers to...Read more
Line chart with many data points
This example shows a line chart plotted with over 8000 data points. The data itself is daily stock market information for Microsoft Corporation over a period of more than 30 years. Only the closing price is plotted. When you first create a line chart with this much data, the x-axis will be crowded with labels. The key is to adjust the bounds and units for the in the Axis options area. In this case, the axis is formatted as a date axis.
Average with multiple criteria
In this example, the goal is to calculate an average for each group and region in the data as shown in the worksheet. For convenience, data is an Excel Table in the range B5:D16. This problem can be easily solved with the...Read more
Only calculate if not blank
The goal is to verify the input of all required values before calculating a result. In the worksheet shown, the SUM function is used as an example only. You can use the same approach with any formula to prevent calculation until all required values are available. The logic can be...Read more
Conditional median with criteria
The MEDIAN function has no built-in way to apply criteria. Given a range, it will return the MEDIAN (middle) number in that range.
To apply criteria, we use the IF function inside MEDIAN to "filter" values. In this example, the IF function filters by group like this:
...Read more
How to lookup first and last match
If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples.Read more