Search results

This formula relies on a helper column, which is column E in the example shown. The formula in E5, copied down, is:

=(COUNTIFS($B$5:B5,B5)=1)+0

This formula returns a 1 for new customers and a 0 for repeat customers, and is...Read more

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

In this example, the goal is to use VLOOKUP to retrieve the price for a given item based on three criteria: name, size, and color, which are entered in H5:H7. For example, for a Blue Medium T-shirt, VLOOKUP should return $16.00.

The VLOOKUP function...Read more

In this example, the goal is to count errors in a workbook by sheet, where the sheet names have been previously entered in a column as shown. This is a tricky problem in Excel for a couple of reasons. First, there is no direct way to generate a list of sheets in a workbook with a formula....Read more

In this example, the goal is to create a formula that performs a dynamic two-way sum of all City and Size combinations in the range B5:D17 . The solution shown requires four basic steps:

1. Create an Excel Table called data
...Read more

In this example, the goal is to create a formula that performs a dynamic two-way count of all color and size combinations in the range B5:D16. The solution shown requires four general steps:

1. Create an Excel Table called
...Read more

In this example, the goal is to create a formula that calculates an average by group, using the group names in column C. The solution shown requires three general steps:

1. Create an Excel Table called data
2. List
...Read more

In this example, the goal is to perform a random sort of the data in B5:B16 with a formula. This can be done with the SORTBY function and the RANDARRAY function.

SORTBY function

The...Read more

In this example, the goal is to create a formula that calculates an average salary by department, where data is an Excel Table in the range B5:D16. The solution shown requires three general steps:

1. Create an Excel
...Read more

In this example, the goal is to create a formula that performs a dynamic two-way average of all age and gender combinations in the range B5:D16 . The solution shown requires four general steps:

1. Create an Excel Table called data
...Read more

Note: FILTER is a new dynamic array function in Excel 365. In other versions of Excel, there are alternatives, but they are more complex...Read more

In this example, the goal is to sum by group, where each group is represented by a different color: Blue, Red, Green, and Purple. The worksheet shown contains two different approaches. In the range F5:G8, we have created a summary table to summarize counts by color. In column D, we are...Read more

This formula uses XLOOKUP twice, by nesting one XLOOKUP inside another. The first (inner) XLOOKUP is used to perform an exact match lookup on the value in G5:

XLOOKUP(G5,E5:E15,B5:E15)

• The
...Read more

In this example, the goal is to strip (i.e. remove) non-numeric characters from a text string with a formula. Until 2024, this was a tricky problem in Excel, partly because Excel did not support regex (Regular Expressions), and partly because there...Read more

This example shows a workbook designed to apply discounts based on seven pricing tiers. The total quantity of items is entered as a variable in cell C4. The discount is applied via the unit costs in E7:E13, which decrease as the quantity increases. The first 200 items have an undiscounted price...Read more