Search results

Although FILTER is more commonly used to filter rows, you can also filter columns, the trick is to supply an array with the same number of columns as the source data. In this example, we construct the array we need with boolean logic, also called Boolean...Read more

In this example, the goal is to calculate an average for any given group ("A", "B", or "C") across all three months of data in the range C5:E16. For convenience only, data (C5:E16) and group (B5:B16) are named ranges. In...Read more

At the core, this formula relies on the SUMPRODUCT function to sum values in matching columns in the named range data C5:G14. If all data were provided to SUMPRODUCT in a single range, the result would be the sum of all values in the range:

...Read more

In this example, the goal is to extract a set of records that match a partial text string. To keep things simple, we are only matching one field in the data, the last name ("Last"). The core operation of this formula comes from the...Read more

In this example, the goal is to count ages in column C according to the brackets defined in columns E and F. All data is in an Excel Table named data defined in the range B5:C16. A simple way to solve this problem is with the COUNTIFS...Read more

In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. The article below explains two approaches. One approach is based on the AVERAGEIFS function, which is designed to calculate...Read more

In the example shown, we want to mark or "flag" records where the color is red OR green. In other words, we want to check the color in column B, and then leave a marker (x) if we find the word "red" or "green". In D6, the formula is:

=IF(OR(B6="red",B6="...Read more

In this example, the goal is to calculate a sum for any given group ("A", "B", or "C") across all three months of data in the range C5:E16. In other words, we want to perform a "sum if" with a data range that contains three columns. For convenience only, data (C5:E16) and...Read more

The #NUM! error occurs in Excel formulas when a calculation can't be performed. For example, if you try to calculate the square root of a negative number, you'll see the #NUM! error. The examples below show formulas that return the #NUM error. In general, the fixing the #NUM! error is a matter...Read more

Excel does not provide a formula function to append or combine ranges, either horizontally or vertically. You can use Power Query for this task, and this makes sense for data transformations that must be automated and repeated on an on-going basis. However, you can also use the...Read more

Note: this formula depends on values in the Time column (C), and values in the Start and End columns (F, G) being valid Excel times.

The data is in an Excel table called table. By...Read more

In this example, the goal is to create a list of pay dates that follow a biweekly schedule. A biweekly pay schedule means employees are paid every two weeks on a given day of the week. Each pay period is 14 days, and there are usually 26 pay dates per year, though occasionally 27 depending...Read more

The core of this formula is a basic lookup with INDEX:

=INDEX(list,row)

In other words, give INDEX the list and a row number, and INDEX will retrieve a value to add to the unique list.

The hard work is figuring out the ROW number to...Read more

In this example, the goal is to count rows using OR logic based on the criteria shown in column F. For example, in cell G5 we want to count rows where Color is "Blue" OR Pet is "Dog". This can be done with Boolean logic and the...Read more

The LARGE function is an easy way to get the nth largest value in a range:

 =LARGE(range,1) // 1st largest
 =LARGE(range,2) // 2nd largest
 =LARGE(range,3) // 3rd largest

In this example, we can use the LARGE function to get a highest score...Read more

The FILTER function is designed to filter and extract information based on logical criteria. In this example, the goal is to extract every 3rd record from the data shown, but there is no row number information in the data.

Working from the inside...Read more

Dates in Excel are serial numbers that start on 1/1/1900, which is represented by the number 1. In the example shown, the formula in cell D6 simply subtracts the numeric value of 1/1/1999 (36161) from the numeric value of 1/1/2000 (36526) to get a result...Read more

The #NAME? error occurs when Excel can't recognize something.  Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just a matter of correcting spelling or a syntax problem. The examples below...Read more

Note: This example assumes the start date will be provided as the first of the month. See below for a formula that will automatically return the first day of the current month.

In this example, the goal is to generate a dynamic calendar for any given month, based on a start date...Read more

In this example, the goal is to count the number of exact matches in two ranges, ignoring the sort order or location of the values in each range. This problem can be solved with the COUNTIF function or with the MATCH function. Each approach is explained below.

Note: Both...Read more

The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function expecting a number receives text value, or when dates are evaluated as text by Excel. Fixing a #VALUE! error is usually just a matter of entering the right kind of...Read more

In this example, the goal is to sum amounts in column C when the date in column B is between two given dates. The start date is provided in cell E5, and the end date is provided in cell F5. The date range should be inclusive - both the start date and end date should be included in the final...Read more