Looking for more? Try chatting with the Exceljet Chatbot.
The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count...Read more
In this example, the goal is to look up the correct shipping cost for an item based on the shipping service selected and the item's weight. At the core, this is an approximate match lookup based on weight. The challenge is that we also need to filter by service. This means we must apply criteria...Read more
The goal is to do something if a cell contains one substring or another. Most users will think first of the IF function. However, one problem with IF is that it does not support wildcards like "?" and "*". This means we can't use IF by itself...Read more
The Name Manager is a dialog box in Excel that allows you to create, edit, and delete defined names. These names can be named ranges, named formulas, and named constants. Using named ranges can make formulas easier to read, understand. and maintain...Read more
For simplicity, we use the term "Legacy Excel" to refer to any version of Excel before dynamic array formulas were introduced. Practically speaking, this means Excel 2019 and older, since Excel 365 and Excel 2021 are the only...Read more
In this example, the goal is to count cells in a range that contain text values. This could be hard-coded text like "apple" or "red", numbers entered as text, or formulas that return text values. Empty cells and cells that contain numeric values or errors should not be included in the...Read more
In this example, the goal is to calculate a conditional sum with the SUMPRODUCT function to match the criteria shown in G5:G7. One way to do this is to use the IF function directly inside of SUMPRODUCT. Another more common alternative is to use Boolean logic to apply criteria. Both approaches...Read more
The goal is to look up the Title, Author, and Year in the list of books as shown using a formula based on a partial match and a wildcard. The text string to search for is entered in cell G4. All data is in an Excel Table named data in the range B5:D16. This problem can be easily solved with the...Read more
Note: Prior to Excel 365, Excel did not have a dedicated function to count unique values. This formula shows one way to count unique values, as long as they are numeric. If you have text values, or a mix of text and numbers, you'll need to use a...Read more
By default, a pivot table shows only data items that have data. When a pivot table is set up to show months, this means that months can "disappear" if the source data does not contain data in that month. In the example shown, a pivot table is used to count the rows by color. There is no data in...Read more
When a filter is applied to a Pivot Table, you may see rows or columns disappear. This is because pivot tables, by default, display only items that contain data. In the example shown, a filter has been applied to exclude the East region. Normally the Blue column would disappear, because there...Read more
Standard Pivot Tables have a simple feature for creating calculated items. You can think of a calculated item as "virtual rows" in the source data. A calculated item will not appear in the field list window. Instead, it will appear as an item in the field for which it is...Read more
You can use a pivot table to display the top or bottom values in a set of data. In the example shown, one pivot table is used to show the top 3 scores in a set of data, and another pivot table is used to show the bottom 3 values in the same set of data. Because one pivot table...Read more
The MINIFS function returns the smallest numeric value that meets supplied criteria, and the MAXIFS function returns the largest numeric value that meets supplied criteria.
Like COUNTIFS...Read more
The SUMPRODUCT function multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. For example, if we give SUMPRODUCT one array...Read more
The goal is to generate a series of "custom" days of the week based on a start date entered in cell B5. For example, you might want to list sequential dates for any of the following combinations of days:
In this example, the goal is to return the most frequently occurring text based on one or more supplied criteria. Working from the inside out, we use the MATCH function to match the text range against itself, by giving MATCH the same range for lookup value...Read more
The goal is to generate a series of sequential weekend days (Saturday and Sunday) with a formula. The start date is entered in cell B5. The number of dates to create (n) is entered in cell B8. If either of these two values are changed, a new list of weekend dates should be generated. In the...Read more
There are eight widely used functions in Excel that use a syntax different from other functions in Excel. This syntax can make these functions more challenging to use, because it is not intuitive. Read on for important information about COUNTIF, COUNTIFS, SUMIF, SUMIFS, ...Read more
The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2.
In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear...Read more
