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: