Count cells that do not contain errors

While working on Excel spreadsheet, you may require to count cells that do not contain errors. You may use NOT functions and ISERR function that is wrapped in SUMPRODUCT function to count error containing cells.

Formula

The formula to count cells that do not contain errors is given below

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

Explanation

In the formula mentioned above, rng corresponds to the range for counting cells containing no errors. SUMPRODUCT count the total number of products and accepts single or multiple arrays. ISERR function gets assessed for every cell included in the range. The result would be an arrangement of values corresponding TRUE or FALSE without NOT function. The double negative operator (–) pushes TRUE values to zero and FALSE values to 1. Then, SUMPRODUCT adds up the things in this arrangement and gives the total count.

Example

In this example, the following formula is used to count cells that do not contain errors.

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

Two cells include errors as shown in the figure while three cells do not contain any errors. Results are achieved by using the above formula in the active cell (see screenshot).

Figure 1 Example of Count cells that do not contain errors in Excel

Notes

To count cells that do not contain errors, SUM function can also be used. The overall configuration of the below-mentioned formula is similar to the above formula. However, Control plus Shift and Enter must be pressed while entering this formula. After that, the formula active cell will be as follow:  

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar