If you want to count the cells in the range that do not contain errors, then we can use the SUMPRODUCT Function, NOT Function, and ISERR Function.
Let’s take an example to elaborate the same:
As you can see in the image above, we have taken a range which has 2 errors and 3 non error cells.
It’s an obvious that you will be having a load of data entries and you won’t be knowing the answer beforehand.
Step 1: Input the formula =SUMPRODUCT(
So, to begin with the procedure, we will input the SUMPRODUCT Function first.
Step 2: Input the required values in the formula
The following formula requires array 1, array 2, array 3, and so on…
Then we will input the minus sign twice. This will be done because we need to separate the errors from the range.
Step 3: Input the formula =NOT(
Then right after the two hyphens, we will input the NOT Function, which is as follows:
Step 4: Input the required values in the formula
The following formula requires logical. Here, logical means the logic we need to imply or the specifications we need to input, so that the NOT Function plays its role accordingly.
Step 5: Input the formula =ISERR(
The logic of the NOT Function will be the ISERR Function.
The following function is as follows:
Step 6: Input the required values in the formula
The following formula requires value.
Here, value in the ISERR Function will be the range from which we need to count the cells that do not contain any errors.
So, as per the above example, the value i.e. the range is B2:B6.
After inputting the value i.e. the range in the ISERR Function, we will close it. And after closing the same, we will close the NOT Function, accordingly the SUMPRODUCT Function as well.
Step 7: Cross-check the formula and proceed
So, overall the formula becomes as follows:
Now all you have to do is press enter, and there you go. The answer is 3.
In the case of keeping a count of the cells which only have errors in the range, all we will do is exclude the NOT Function which makes the overall formula as follows:
Now, all you have to do is press enter, and BAAAAMMM!!! The answer is 2, there are 2 errors in the range.
It’s easy, isn’t it?