Count cells that do not contain errors

Generic formula 

=SUMPRODUCT(--NOT(ISERR(rng)))

Explanation 

To count the number of cells that contain errors, you can use the ISERR and NOT functions, wrapped in the SUMPRODUCT function. In the generic form of the formula (above) rng represents the range in which you'd like to count cells with no errors.

In the example, the active cell contains this formula:

=SUMPRODUCT(--NOT(ISERR(B4:B8)))

How this formula works

SUMPRODUCT accepts one or more arrays and calculates the sum of products of corresponding numbers. If only one array is supplied, it just sums the items in the array.

The ISERR function is evaluated for each cell in rng. Without the NOT function, the result is an array of values equal to TRUE or FALSE:

{TRUE;FALSE;TRUE;FALSE;FALSE}

With the NOT function, the result is:

{FALSE;TRUE;FALSE;TRUE;TRUE}

This corresponds to cells that do not contain errors in the rng.

The -- operator (called a double unary) forces the TRUE/FALSE values to zeros and 1's. The resulting array looks like this:

{0;1;0;1;1}

SUMPRODUCT then sums the items in this array and returns the total, which in the example is the number 3.

You can also use the SUM function to count errors. The structure of the formula is the same, but it must be entered as an array formula (press Control + Shift + Enter instead of just Enter). Once entered, the formula will look like this:

{=SUM(--NOT(ISERR(B4:B8)))}

Don't enter the braces {}, they are entered for you when you press Control + Shift + Enter.

JimpakChipak

Count cells that do not contain

Generic formula 

=COUNTIF(rng,"<>*txt*")

Explanation 

To count the number of cells that do not contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should not contain, and "*" is a wildcard matching any number of characters.

In the example, the active cell contains this formula:

=COUNTIF(B4:B11,"<>*a*")

How this formula works

COUNTIF counts the number of cells in the range that do not contain "a" by matching the content of each cell against the pattern "<>*a*", which is supplied as the criteria. The "*" symbol (the asterisk) is a wildcard in Excel that means "match any number of characters" and "<>" means "does not equal", so this pattern will count any cell that does not contain "a" in any position. The count of cells that match this pattern is returned as a number.

With a cell reference

You can easily adjust this formula to use the contents of another cell that contains the text you do not want to count. The generic form of the formula looks like this:

=COUNTIF(rng,"<>*"&a1&"*")

Exclude blanks

To also exclude blank cells, you can switch to COUNTIFS and add another criteria like this:

=COUNTIFS(range,"<>*a*",range,"?*") // requires some text




JimpakChipak

Count cells that contain text

Generic formula 

=COUNTIF(rng,"*")

Explanation 

To count the number of cells that contain text (i.e. not numbers, not errors, not blank), use the COUNTIF function and a wildcard. In the generic form of the formula (above), rng is a range of cells, and "*" is a wildcard matching any number of characters.

Do you want to count cells that contain specific text? See this formula instead.

In the example, the active cell contains this formula:

=COUNTIF(B4:B8,"*")

How this formula works

COUNTIF counts the number of cells that match the supplied criteria. In this case, the criteria is supplied as the wildcard character "*" which matches any number of text characters.

A few notes:

• The logical values TRUE and FALSE are not counted as text
• Numbers are not counted by "*" unless they are entered as text
• A blank cell that begins with an apostrophe (') will be counted.

You can also use SUMPRODUCT to count text values along with the function ISTEXT like so:

=SUMPRODUCT(--ISTEXT(rng))

The double hyphen (called a double unary) coerces the result of ISTEXT from a logical value of TRUE or FALSE, to 1's and 0's. SUMPRODUCT then sums these values together to get a result.

Count cells that contain specific text

Generic formula 

=COUNTIF(rng,"*txt*")

Explanation 

To count the number of cells that contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should contain, and "*" is a wildcard matching any number of characters.

In the example, the active cell contains this formula:

=COUNTIF(B4:B11,"*a*")

How this formula works

COUNTIF counts the number of cells in the range that contain "a" by matching the content of each cell against the pattern "*a*", which is supplied as the criteria. The "*" symbol (the asterisk) is a wildcard in Excel that means "match any number of characters", so this pattern will count any cell that contains "a" in any position. The count of cells that match this pattern is returned as a number.

You can easily adjust this formula to use the contents of another cell for the criteria. For example, if A1 contains the text you want to match, use the formula:

=COUNTIF(rng,"*"&a1&"*")

Case-sensitive version

If you need a case-sensitive version, you can't use COUNTIF. Instead you can test each cell in the range using a formula based on the FIND function and the ISNUMBER function, as explained here.

FIND is case-sensitive, and you'll need to give it the range of cells and then use SUMPRODUCT to count the results. The formula looks like this:

=SUMPRODUCT(--(ISNUMBER(FIND(text,rng))))

Where text is the text you are looking for, and rng is the range of cells you want to count. There's no need to use wildcards, because FIND will return a number if text is found anywhere in the cell.