Search results

In this example, the goal is to count test scores in column C that are greater than 90. The simplest way to do this is with the COUNTIF function, which takes two arguments, range and criteria...Read more

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D15, which contains all of the data without headers. The include argument is...Read more

Starting from the inside out, the MID function is used to extract all text after "@":

MID(B5,FIND("@",B5),LEN(B5))

The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text....Read more

In this example, the goal is to look up the correct price of the product number entered in cell F4 using the product codes in column B. This problem is trickier than it looks. Each product code begins with 3 uppercase letters and ends with 2 or 3 uppercase letters. In the middle of the...Read more

This example is set up in two parts for clarity: (1) a formula to determine the top 3 amounts for each month and (2) a formula to retrieve the client name for each of the top 3 monthly amounts.

Note there is no actual rank in the source data. Instead, we are using the LARGE function to...Read more

This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is:

type,G$4

where type is the named range D5:D14...Read more

The goal is to mark rows where the color is "Red" AND the size is "Small" or "Medium". To perform this task, you can use the IF function in combination with the AND function and the...Read more

The CELL function can provide a wide range of information about cell properties. One property is called "protect" and indicates whether a cell is unlocked or locked. All cells start out "locked" in a new Excel workbook, but this setting has no effect until a worksheet is protected.

The...Read more

In this example, the goal is to count rows in a set of data using multiple criteria and "not equals to" logic. Specifically, we want to count males that are not in group A or B. All data is in an Excel Table named data in the range B5:...Read more

First, you should know that Excel contains two functions, CLEAN and TRIM, that can automatically remove line breaks and extra spaces from text. For example to strip all line breaks from a cell, you could use:

=CLEAN(B5)

For a quick demo of...Read more

In this example, the goal is to sum values in matching columns and rows. Specifically, we want to sum values in data (C5:G14) where the column code is "A" and the day is "Wed". One way to solve this problem is with the SUMPRODUCT function...Read more

In this example, we have comma-separated text in column B. The goal is to split the text in column B into columns D through G while at the same time converting the numbers to true numeric values. The challenge is that TEXTSPLIT always returns text, so we need a way to convert the numbers...Read more

The FILTER function can filter data using a logical expression provided as the include argument. In this example, this argument is created with an expression that uses the ISNUMBER and MATCH functions like this:

...Read more

The goal is to display a checkmark (also called a "tick mark" in British English) when a task is marked complete. The easiest way to do this is with the IF function and the mark you would like to display. The article below explains several options.

IF with a plain checkmark

The...Read more

This formula uses the MIN function to make a decision that might otherwise be handled with the IF function. Although MIN is usually used to return the minimum value in a data set with many numbers, it also works fine for...Read more

In this example, the goal is to count test scores in column C that are less than 75. The simplest way to do this is with the COUNTIF function, which takes two arguments, range and criteria...Read more

This example uses the UNIQUE function. With default settings, UNIQUE will output a list of unique values, i.e. values that appear one or more times in the source data. However, UNIQUE has an optional third argument, called occurs_once that, when...Read more

The example shown contains almost 10,000 rows of data. The data represents temperature readings taken every 2 minutes over a period of days. For any given date (provided in cell H7), we want to get the maximum temperature on that date.

Inside the IF...Read more

In this example, the goal is to demonstrate how an INDEX and (X)MATCH formula can be set up so that the columns returned are variable. This approach illustrates one benefit of the 2-step process used by INDEX and MATCH: Because INDEX expects a numeric index for row and column numbers, it is easy...Read more

Excel times are numbers and can be summed like other numeric values. In this example, F4:G7 is a summary table, showing the total time logged in each of three states: Standby, Run, and Offline. These values are hardcoded in the range F5:F7.

To sum...Read more

At the core, the OFFSET function delivers a range of 3 cells to SUM, which returns a summed result.

The arguments for OFFSET are provided as follows:

For reference we use the first cell in the data range, B5, entered as a ...Read more

This formula is an interesting example of a "brute force" approach that takes advantage of the fact that TRIM will remove any number of leading spaces.

Working from the inside out, we use the SUBSTITUTE function to find all spaces in the text, and replace each space with 100...Read more

This formula concatenates the original value in column B to a string of asterisks (*) assembled with the REPT function so that the final result is always 12 characters:

REPT("*",12-LEN(B5))

Inside the REPT function, the text to repeat is...Read more

Dates in Excel are just serial numbers, formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past.

In the example shown, the date in the named range "start" is provided by the TODAY...Read more

The goal is to mark records with an "x" when the color is "Red" and the size is "Small". To perform this task, you can use the IF function in combination with the AND function.

IF function

The...Read more

The COUNTIF function counts the number of times each value appears in the data range. By definition, each value must appear at least once, so when the count equals 1, the value is unique. When the count is 1, the formula returns TRUE and triggers the...Read more

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