Looking for more? Try chatting with the Exceljet Chatbot.
In this example, the goal is to count the number of cells in a range that are over a certain number of characters in length, where the number (n) is provided as a variable in cell F4. This problem can be solved with the SUMPRODUCT and LEN functions like this:
...Read more
A common situation in Excel is to use the SORT function to sort results returned by the FILTER function. However, a formula based on the FILTER and SORT may return an error when no data is returned. In this...Read more
The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.Read more
In this example, the goal is to list the next n upcoming birthdays from a larger set of 25 birthdays based on the current date. The set of birthdays are in an Excel Table named data in the range B5:C29. In the example shown, we are using 7 for...Read more
The Excel RANK.AVG function returns the rank of a number against a list of other numeric values. When values contain duplicates, the RANK.AVG function will assign an average rank to each set of duplicates....Read more
The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the "nth occurrence" and the TRIM function to get rid of the extra space.
In this snippet, the delimiter (delim...Read more
In this example, the goal is to repeat a range of values. This can be done in various ways in Excel, but I think the CHOOSEROWS/ CHOOSECOLS functions are the easiest way to retrieve values from the range for now. Both functions work natively with two-dimensional ranges and can accept a...Read more
The Excel FIXED function converts a number to text with fixed number of decimals, rounding as needed with the given number of decimals. The FIXED function can be useful when concatenating a formatted number text.Read more
In this example, criteria are entered in the range F5:H6. The logic of the formula is:
item is (tshirt OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle)
The filtering logic of this formula (the include argument) is applied with the...Read more
The Excel COLUMNS function returns the count of columns in a given reference. For example, COLUMNS(A1:C3) returns 3, since the range A1:C3 contains 3 columns.Read more
At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings, Sunday=1 and Saturday = 7.
The trick to this formula is assembling...Read more
The Excel COLUMN function returns the column number for a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. When no reference is provided, COLUMN returns the column number of the cell which contains the formula.Read more
At the core, this formula uses the MID function to extract characters starting at the second to last space. The MID function takes 3 arguments: the text to work with, the starting position, and the number of characters to extract.
The text comes from column B, and the number of...Read more
The Excel DETECTLANGUAGE function detects the language of a given text string. The result is a language code. For example, if the language is English, the result is "en"; if the language is French, the result is "fr"; if the language is German, the result is "de", and so on. ...Read more
This formula first generates a rank value using an expression based on COUNTIF:
=COUNTIF(data,"<="...Read moreThis page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. They are an alternative to using an...Read more
The Excel XMATCH function performs a lookup and returns a position of a value in a range. It is a more robust and flexible successor to the MATCH function. XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?) for partial matches. ...Read more
In this example, the goal is to mark dates in column D with an "x" when they have the same year and month as the date in cell B5. We don't care at all about the day. At a high level, we can easily use the IF function to return "x" for qualifying dates, so the challenge is in creating a logical...Read more
In this example, the goal is to sum the numbers in column E when the item in column B appears in the range G5:G7. The named range things is not required. It is used only for convenience and can be expanded as needed to include additional criteria. The article below explains...Read more
This formula uses the COUNTA function to count values in a range. COUNTA counts both numbers and text to so works well with mixed data.
The range B4:B8 contains 5 values, so COUNTA returns 5. The number 5 corresponds to the last row (last relative position) of data in the range B4:B100...Read more
Imagine a company that uses a tiered commission structure for its sales team. Each salesperson is assigned a commission rate based on the total sales they have made. The commission tiers are structured like this:
In the example shown, the formula in B11 is:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")
Working from the inside out, the work of setting up a variable range is done by the OFFSET function here:
...Read more
The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.Read more
This formula relies on the AND function to test two conditions at the same time:
If both conditions are TRUE, the formula returns TRUE...Read more
The Excel OR function is a logical function used to test multiple conditions at the same time. OR returns TRUE if any condition is TRUE. If all conditions are FALSE, the OR function returns FALSE. The OR function is often used with the IF function and can be combined...Read more
Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:
$B3=$E$5
The second test is more complex:...Read more
In this example, the goal is to highlight rows in the data shown when the date is a specific day of week. The target day of week is a variable selected with a dropdown menu in cell F5, which contains abbreviated day names. This problem can be easily solved by applying conditional formatting...Read more
In this example, the goal is to count rows where the value in column one is "A" or "B" and the value in column two is "X", "Y", or "Z". In the worksheet shown, we are using array constants to hold the values of interest, but the article also shows how to...Read more
The Excel TEXTSPLIT function splits text by a given delimiter to an array that spills into multiple cells. TEXTSPLIT can split text into rows or columns.Read more
The LOOKUP function assumes data is sorted, and always does an approximate match. If the lookup value is greater than all values in the lookup array, default behavior is to "fall back" to the previous value. This formula exploits this behavior by creating an array that contains only 1s and...Read more