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 formulas...Read more
In this example, the goal is to count the number of cells in a range that contain formulas. This problem can be solved with a formula based on the SUMPRODUCT and ISFORMULA functions, as explained below.
The values in the range C13:C16 are forecasts created...Read more
The double negative coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. This is a useful technique in many advanced formulas that work with cell ranges.Read more
At the core, this formula has a simple pattern like this:
=SUM(first:current)
Where "first" is the first cell in the Total column, and "current" is a reference to a cell in the current row of the Total column.
To get the a...Read more
This article explains the function list separator used in Excel functions.
By default, Excel uses the list separator defined under regional settings in Control Panel. The US English version of Excel uses a comma (,) for list separator by default, while other international versions may...Read more
The Excel MONTH function extracts the month from a given date as a number between 1 and 12. You can use the MONTH function to extract a month number from a date into a cell or to feed a month number into another function like the DATE function....Read more
In this example, the goal is to subtotal invoices by age, where age represents the number of days since the invoice was issued. This problem can be solved with the SUMIFS function and the COUNTIFS function, as explained below. For convenience, age (E5:E16) and amount...Read more
If you are new to the SUMIFS function, you can find a basic overview with many examples here.
The SUMIFS function is designed to sum numeric values based on one or more criteria. In specific cases however, you may be able to use SUMIFS to "look up...Read more
The Excel TEXTAFTER function returns the text that occurs after a given substring or delimiter. In cases where multiple delimiters appear in the text, TEXTAFTER can return text after the nth occurrence of a delimiter....Read more
The Excel TEXT function returns a number in a given number format, as text. You can use the TEXT function to embed formatted numbers inside text.Read more
The Excel ISFORMULA function returns TRUE if a cell contains a formula, and FALSE if not. When a cell contains a formula ISFORMULA will return TRUE regardless of the formula's output or error conditions.Read more
In this example, the goal is to copy every nth value from column B, where n is a variable that can be changed as needed.
In Excel, it's difficult to create formulas that skip rows following a certain pattern, because the references in the formula will automatically change as the formula...Read more
In this example, the goal is to return an array with 7 subtotals, one for each day of the week, as seen in columns C:I. The numbers to sum are contained in data which is the named range C5:I13. This is an example of a problem where the goal is...Read more
In this example, the goal is to return an array with nine subtotals, one for each of the colors named in column B. The numbers to sum are contained in data which is the named range C5:I13. This is an example of a problem where the goal is to...Read more
The Excel AGGREGATE function returns an aggregate calculation like AVERAGE, COUNT, MAX, etc., optionally ignoring hidden rows and errors. A total of 19 operations are available, specified by function number in the first argument (see table for options).Read more
In this example, the goal is to create a dynamic reference to an Excel Table in a formula. In other words, create a formula that can refer to an Excel table by name as a variable. The easiest way to do this in Excel is to assemble the reference as a text value...Read more
The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.Read more
Note: in more recent versions of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER...Read more
In this example, the goal is to count the number of columns in the data that contain 19 (the value in cell I4). The main challenge in this problem is that the value might appear in any row, and more than once in the same column. If we wanted to simply count the total number of times a value...Read more
One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list. But how can you make one dropdown dynamically respond to another? In other words, how can you make the values in a dropdown list depend on...Read more
In this example the goal is to "unwrap" a column of values into separate fields. The values are spaced evenly apart, and the result should be all related values on one row, where each column corresponds to a field of information. The input data appears in column B. Each "...Read more
The Excel TEXTBEFORE function returns the text that occurs before a given substring or delimiter. In cases where multiple delimiters appear in the text, TEXTBEFORE can return text before the nth occurrence of the delimiter.Read more
In this example, the goal is to work out the total of all expenses using a known percent of total of any one expense. If we know groceries are $200 and we know groceries represent 10.3% of total expenses, we want to calculate the total of all expenses ($1945). In other words, $...Read more
Learn to use Excel formulas and functions to build powerful, efficient spreadsheets. This accelerated video training guides you step-by-step.Read more
The Excel FILTERXML function returns specific data from XML text using the specified XPath expression.Read more
The SUMIFS function is designed to sum numeric values using multiple criteria. In the example shown, the data in the range B5:E15 shows a sales pipeline where each row is an opportunity owned by a salesperson, at a specific stage. The formula in H5 is:...Read more
Every day, thousands of questions about Excel are posted on the internet. Many of these questions go unanswered because they are unclear. This article contains some tips on how to write a question that people will quickly understand and answer if they can.Read more