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 goal is to do something if a cell contains a given substring. For example, in the worksheet above, a formula returns "x" when a cell contains "abc". If you are familiar with Excel, you will probably think first of the IF function. However, one limitation of IF is that it does...Read more
In this example, the goal is to count the number of items sold and remaining, based on the data visible in columns B and C. The ID column holds unique ids, and the Sold column is used to record a sale. An "x" in the Sold column indicates the item has been sold. As is typical in Excel, there are...Read more
This formula builds the final result in 2 parts, joined by concatenation with the ampersand (...Read more
The WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. WEEKNUM takes two arguments: a date, and (optionally) ...Read more
The WORKDAY.INTL function calculates a date in the future or past that is a given number of working days from a specified start date, excluding weekends and (optionally) holidays. You can WORKDAY.INTL to calculate project start dates, delivery dates, and completion dates that must ignore non-...Read more
The NETWORKDAYS.INTL function returns the number of working days between two dates, taking into account holidays and weekends. This function is more robust than the NETWORKDAYS function because it allows you to control which days of the week are...Read more
If you need to group times into buckets that are not the same size (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.) you can use the VLOOKUP function in approximate match mode.
There are several ways to group times in Excel. If you just need to group times by the hour, a pivot...Read more
The goal of this example is to test each cell in B5:B14 to see if it contains any of the strings in the named range things (E5:E7). These strings can appear anywhere in the cell, so this is a literal "contains" problem. The...Read more
A common annoyance with data is that it may be represented as text instead of numbers. This is especially common with dimensions, which may appear in one text string that includes units, for example:
50 ft x 200 ft
153 ft x 324 ft
Etc.
In a...Read more
