Search results

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, we have a simple set of data in B5:D16 that includes ID, Name, and Points. The goal is to generate a custom message for any name in the list by entering a valid ID in cell G5. The message uses the name from column C and the points in column D like this:

...Read more

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

In this example the goal is to check a cell for several things at once, and return a comma separated list of the things that were found.  In other words, we want check for the colors seen in column E and list the colors found in column C. The formula in C5, copied down, is:

...Read more

In this example, the goal is to count numbers that fall within specific ranges. The lower value comes from the "Start" column, and the upper value comes from the "End" column. For each range, we want to include both the lower value and the upper value. For convenience, the numbers being...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

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 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

The goal of this example is to count rows in the data where the date joined falls between start and end dates (inclusive) and the age also falls into the age ranges seen in column G. The formula is complicated somewhat by the fact that the age range labels are actually text, so we need to...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