Search results

In this example, the goal is to clean up telephone numbers with inconsistent formatting and then reformat the numbers in the same way. In practice, this means we need to start by removing the extra non-numeric characters, including spaces, dashes, periods, and parentheses. Once these...Read more

In this example, the goal is to count cells that do not contain a specific substring. This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both approaches...Read more

The goal is to use a formula to check if all cells in a row are blank or empty and return TRUE or FALSE. One way to solve this problem is with the SUMPRODUCT function, as seen in the worksheet above. Another approach is to use the newer BYROW function. Both methods are described below....Read more

Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts instances of different keywords.

The core of this formula is the ISNUMBER + SEARCH approach to finding text in a cell, which is...Read more

The first ROW function generates an array of 7 numbers like this:

{5;6;7;8;9;10;11}

The second ROW function generates an array with just one item like this:

{5}

which is then...Read more

In this example, the goal is to count visible rows where Region="West". Row 13 meets this criteria, but has been hidden. The SUBTOTAL function can easily generate sums and...Read more

Traditionally, counting unique items with an Excel formula has been a tricky problem, because there hasn't been a dedicated unique function. However, that changed when dynamic arrays were added to Excel 365, along with several new functions...Read more

Note: the core idea of this formula is adapted from an example in Mike Girvin's excellent book Control+Shift+Enter.

The example shown uses several...Read more

In this example, the goal is to sum the quantities in column C when the text in column B contains "hoodie". The challenge is that the item names ("Hoodie", "Vest", "Hat") are embedded in a text string that also contains size and color. This means we need to apply criteria that...Read more

In this example, the goal is to sum Amounts in column C by state using the two-letter codes in column E. Note the states are abbreviated, "CA" is California, "FL" is Florida, "TX" is Texas, and "WA" is Washington. The challenge in this case is that the state abbreviations are embedded...Read more