Search results

In this example, the goal is to sum the numbers in column C when the text in column B contains specific pairs of colors. For example, the formula should sum a number when the text contains both "red" and "blue". Order is not important; the two colors can appear anywhere in the cell. However...Read more

For each cell in the range, SUBSTITUTE removes all the o's from the text, then LEN calculates the length of the text without o's. This number is then subtracted from the length of the text with o's.

Because we are using SUMPRODUCT, the result of all this calculation is a list of items (...Read more

In this example, the goal is to sum the quantities in column C by the codes in column E in a case-sensitive manner. The SUMIF function and the SUMIFS function are both good options for counting text values, and...Read more

In this example, the goal is to test a value in a cell to see if it contains a specific substring. Excel contains two functions designed to check the occurrence of one text string inside another: the SEARCH function and the FIND function. The difference is that the SEARCH function...Read more

The core of this formula is the COUNTA function, configured with an expanding range like this:

COUNTA($B$5:B5)

As the formula is copied down the column, the range starting with B5 expands to...Read more

In this example, the goal is to test values in column B to see if they begin with a specific text string, which is "xyz" in the worksheet shown. This problem can be solved with the LEFT function, as explained below.

LEFT function

The LEFT...Read more

In this example, the goal is to test values in column B to see if they end with a specific text string, which is "jwb" in the worksheet shown. This problem can be solved with the RIGHT function, as explained below.

RIGHT function

The RIGHT...Read more

In this example, the goal is to count numbers that contain leading zeros. In cell E5, we have the code "009875" and we want to count how many times this code appears in the range B5:B16. The challenge is that Excel can be finicky with leading zeros. Technically, the values in B5:B16 are...Read more

In this example, the goal is to return a count for each color that appears in column C, using the color values already in column E as criteria. When working with data, a common need is to perform summary calculations that show total counts in different ways. For example, total counts by...Read more

First, the LEN function counts total characters in the cell B5.

Next SUBSTITUTE removes all "line returns" from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows. LEN returns the result inside of a second LEN, which counts characters...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

In this example, the goal is to create automatic row numbers starting in cell B5 that match the data entered in column C. When new data is added to the list, the row numbers should increase as required. If items are deleted, the row numbers should respond accordingly. This has traditionally been...Read more

In this example the goal is to count numbers longer than 15 digits with a formula. The COUNTIF function may seem like this logical choice. However, if you try to count very long numbers (16+ digits) in a range with the COUNTIF function, you may see...Read more

In this example, the goal is to count numbers in the range B5:B15 (named data) where the third digit is a specific number, indicated in column D. You might think the COUNTIF function would be a good way to solve this problem. However, for ...Read more

The goal is to return the full address of a range or named range as text. The purpose of this formula is informational, not functional. It provides a way to inspect the current range for a named range, an Excel Table, or even the result of a formula. The core...Read more

By default, Excel is not case-sensitive.  For example, with "APPLE" in  A1, and "apple" in A2, the following formula will return TRUE:

=A1=A2 // returns TRUE

To compare text strings in a case-sensitive way, you can use the...Read more

In this example, the goal is to test if a given range contains duplicate values and return TRUE if duplicates exist and FALSE if not. This is essentially a counting problem and the solution is based on the COUNTIF function, which counts values in a range...Read more