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
In this example, the goal is to count test scores in column C that are less than 75. The simplest way to do this is with the COUNTIF function, which takes two arguments, range and criteria...Read more
In this example, the goal is to create a formula that performs a dynamic two-way count of all color and size combinations in the range B5:D16. The solution shown requires four general steps:
In this example, the goal is to count codes in a case-sensitive way. The COUNTIF function and the COUNTIFS function are both good options for counting text values, but neither is case-sensitive, so they can't be...Read more
In this example, the goal is to count rows in a set of data using multiple criteria and "not equals to" logic. Specifically, we want to count males that are not in group A or B. All data is in an Excel Table named data in the range B5:...Read more
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.Read more
In this example, the goal is to count the number of cells in column D that contain dates that are between two variable dates in G4 and G5. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function, as explained below. For convenience, the worksheet contains two...Read more
First, the LEN function counts total characters in the cell B5.
Next SUBSTITUTE removes all "line returns" from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows. LEN returns the result inside of a second LEN, which counts characters...Read more
The Excel FREQUENCY function returns a frequency distribution, which is a list that shows the frequency of values at given intervals. FREQUENCY returns multiple values and must be entered as an array formula with control-shift-enter, except in...Read more
This article explains how to concatenate manually with the ampersand operator (&) and with the three Excel functions designed for concatenation: CONCATENATE, CONCAT, and TEXTJOIN.Read more
In this example, the goal is to count cells in the range B5:B15 that contain either "x" or "y", where x and y are both text strings. When you count cells with "OR logic", you need to be careful not to double count. For example, if you are counting cells that...Read more
In this example, the goal is to build a simple summary count table with a formula. Once created, the summary table should automatically update to show new values and counts when data changes. The article below walks through several options, from simple to very advanced. The more advanced options...Read more
Named ranges make formulas easier to read, faster to develop, and more portable. They're also useful for data validation, hyperlinks, and dynamic ranges. This article shows you how you can use named ranges to build better spreadsheets, and better formulas.Read more
Dynamic Array Formulas are one of the biggest changes to Excel ever. They completely change the way you solve hard problems in Excel, with excellent new functions and an upgraded formula engine that can return multiple results at the same time. With Dynamic Arrays, you can extract...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
This formula is evaluated for each of the 10 cells in A1:D10. A1 will change to the address of the cell being evaluated, while C1:C10 is entered as an absolute address, so it won't change at all.
The key to this formula is the =0 at the end, which "flips" the logic of the formula. For...Read more
In this example, the goal is to count non-blank dates in column D by group. All data is an Excel Table named data in the range B5:D16. This problem can be solved with the COUNTIFS function, as...Read more
Dynamic Arrays are the biggest change to Excel formulas in years. Maybe the biggest change ever. This is because Dynamic Arrays let you easily work with multiple values at the same time in a formula. This article provides an overview with many links and examples.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
The goal is to identify invoice numbers in range D5:D11 that are missing in range B5:B16 (named list). Two good ways to solve this problem in Excel are the COUNTIF function and the MATCH function....Read more
In this example, the goal is to generate a list of people who were invited but did not attend an unspecified event. More specifically, we need to compare the names in B5:B16 against the names in D5:D12 and return the missing names. For convenience, list1 (B5:B16) and...Read more