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.


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



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.


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


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


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:  


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