Count cells that do not contain errors

sumproduct-based-solution

To count cells that do not contain errors, use the SUMPRODUCT or COUNTIFS function, which works with different data types in a given range.

How to count cells that do not contain errors?

Steps to count error-free cells in a range:

  1. Open Excel
  2. Type =SUMPRODUCT(–NOT(ISERROR(B3:B12)))
  3. Press Enter
  4. The formula returns the number of cells that do not contain errors.

Example

The formula in cell D3:

=SUMPRODUCT(–NOT(ISERROR(B3:B12)))

First, evaluate the formula from the inside out. The ISERROR function checks errors in a given range and returns an array that contains boolean values. That means the array contains TRUE or FALSE values. In the case of TRUE, the cell contains an error.

sumproduct-based-solution

={FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE}

The NOT function reverses the result in the array. Use the double-negative method to convert boolean values to 1s and 0s.

={1, 1, 0, 1, 1, 0, 1, 1, 1, 1}

Finally, the SUMPRODUCT-based formula sums the 1s in the array and returns 8.

Using COUNTIFS function

In the worst case, you can use the COUNTIF and COUNTIFS functions to count cells that do not contain errors. For example, you can find cells that do not contain the #DIV/0! error.

=COUNTIF(B3:B12,”<>#DIV/0!”)

What if you want to check all error types? A painful and elaborate workaround is waiting for you. To use multiple criteria, apply the COUNTIFS function that uses range-criteria pairs.

=COUNTIFS(B3:B12,”<>#DIV/0!”,B3:B12,”<>#VALUE!”)

So, if you want to write a formula that contains all Excel cell error types, you can write a long COUNTIFS formula. It is good to know that in Excel, we have 10 types of errors that can appear in the cell.

Now, let us see how the COUNTX function works.

Count error-free cells using COUNTX

We will introduce the COUNTX function, a Swiss knife in Excel if you want to count cells by type. With its help, you can write short and effective formulas.

To count cells that do not contain errors, use the steps below:

  1. Type the =COUNTX(B3:B12, 7) formula in cell D3
  2. Press Enter
  3. The formula will count cells that do not contain errors.

COUNTX provides versatile COUNT functions and supports search types for the following cases: Text, Numeric, Date, Boolean, Error, Formula, and Error-free cells.

data-types

The COUNTX function is a part of our user-defined function library, DataFX. Please take a closer look at the add-in; it is worth it.

In the example, our data set is in the range B3:B12. The goal is to count the number of “error-free” cells.

Formula:

=COUNTX(range, 7)

Type the following formula in cell D3:

=COUNTX(B3:B12,7)

count-cells-that-do-not-contain-errors-in-Excel

The formula returns 8 since two cells contain errors, #VALUE! in cell B5 and #DIV/0! in B8.

Final words

User-defined functions provide quick solutions for all Excel challenges. We built an add-in and implemented 200 power functions to support your work in Microsoft Excel. You can download the add-in and share the knowledge to support development. Thanks for being with us today!