To count cells that do not contain errors, use the COUNTX function, which works great with different data types in a given range.
First, 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.
How to count cells that do not contain errors
- Type the =COUNTX(B3:B12, 7) formula in cell D3
- Press Enter
- 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.
The COUNTX function is a part of our user-defined function library, DataFX. Take a closer look at the add-in; it is worth it.
Example
In the example, our data set is in the range B3:B12. The goal is to count the number of “error-free” cells.
Generic formula:
=COUNTX(range, 7)Type the following formula in cell D3:
=COUNTX(B3:B12,7)
The formula returns 8 since two cells contain errors, #VALUE! in cell B5 and #DIV/0! in B8.
Workaround with the SUMPRODUCT function
Using SUMPRODUCT, you can write a complex formula that counts cells that do not contain errors.
The formula in cell D3:
=SUMPRODUCT(--NOT(ISERROR(B3:B12)))
Here is the explanation in a nutshell:
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.
={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.
Count cells that do not contain errors using COUNTIFS
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. Good to know that in Excel, we have 10 types of errors that can appear in the cell. Again, we recommend you use COUNTX in performing this task.
Final words
User-defined functions provide quick solutions for all challenges in Excel. We built an add-in and implemented 200 power functions to support your work in Microsoft Excel. You can download the add-in and support the development process by sharing the knowledge. Thanks for being with us today!
Related formulas:
