Search results

In this example, the goal is to count cells in a range that are not blank (i.e. not empty). There are several ways to go about this task, depending on your needs. The article below explains different approaches.

COUNTA function

While the...Read more

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

Excel's formula engine has some quirks that you should be aware of. One of these quirks is that Excel will treat a text value as larger than a number by default. For example:

=90>100 // returns FALSE
="A">100 // returns TRUE

The second...Read more

One of the trickier problems in Excel is to count rows in a set of data with "OR logic". There are two basic scenarios: (1) you want to count rows where a value in a column is "x" OR "y" (2) you want to count rows where a value, "x", exists in one column OR another.

In this example, the...Read more

Note: the core idea of this formula is adapted from an example in Mike Girvin's excellent book Control+Shift+Enter.

The example shown uses several...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

In this example, the goal is to count cells that contain a specific substring. This problem can be solved with the SUMPRODUCT function or the COUNTIF function. Both approaches are explained below. The SUMPRODUCT version can also perform a case-sensitive count.

COUNTIF function...Read more

In this example, the goal is to count cells in the range D5:D15 that contain "red" or "blue". For convenience, the D5:D15 is named color. Counting cells equal to this OR that is more complicated than it first appears because there is no built-...Read more

In this example, the goal is to count the total number of words in a cell. Excel doesn't have a dedicated function for counting words. However, with a little ingenuity, you can create a formula to perform this task using a combination of built-in functions. In newer versions of Excel, the best...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 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 count codes that appear as substrings in a case-sensitive way. The functions COUNTIF and COUNTIFS are both good options for counting text values, but these functions are not case-...Read more

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 cells in the range B5:B16 that begin with specific text, which is provided in column D. For convenience, the range B5:B16 is named data. 

COUNTIF function

The simplest way to solve this problem is with the ...Read more

In this example, the goal is to count cells equal to a specific value. In this case, we want to count cells that contain "red" in the range D5:D16. This problem can be solved with the COUNTIF function and the SUMPRODUCT function, as explained below.

...Read more

When working with Excel, there are times when you need to determine if a range of cells is empty. This can be useful in various scenarios, such as data validation, error checking, or report preparation. In this article, we'll explore a couple of formulas that can help you check if all cells in a...Read more

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each cell in B4:G12, and the reference to B4 will change to the address of each cell being...Read more

The goal is to count the number of cells in a given range, regardless of whether the cells are empty or not. Although Excel has several functions designed to count cells based on their contents, there is no built-in function for counting the total number of cells in a range. The classic...Read more

In this example, the goal is to count cells in a range that are blank. Counting blank cells in Excel can be tricky because cells can look blank even when they are not actually empty. The article below explains three different approaches.

...Read more

In this example, the goal is to count the number of occurrences of a character in a cell or text string. Strangely, Excel does not have a function dedicated to counting characters, so we need to use a formula that computes a count manually. The typical way to do this is to use a formula...Read more

In this example, the goal is to sum numbers in the range C5:C16 when text in the range B5:B16 contains the substring "red" OR the substring "blue". In other words, if the text in B5:B16 contains either of these two text values in any location, the corresponding number in C5:C16 should be...Read more

In this example, the goal is to use the COUNTIFS function to count data with "OR logic". The challenge is the COUNTIFS function applies AND logic by default.

COUNTIFS function

The COUNTIFS function returns the count of cells...Read more

In the generic version of the formula, rng represents the range to check, and txt is the word or substring to count.

In the example shown, B5:B8 is the range to check, and C2 contains the text (word or substring) to count.

For each cell in the...Read more