In this chapter, we will learn how to count errors in excel using different count functions and logical functions like IF with an error detection function called ISERROR. You can browse the video above and learn while watching it or you can browse the article below to learn the same.

Download Practice Sheet

Below we have sample data for learning purposes where we have incurred various excel errors in our column A.

Count Errors In Excel in 2 Simple Steps 15

STEP1 – Detect Errors Using ISERROR Function

There are various ways to count errors in excel and one of its ways are ISERROR Function.

Count Errors In Excel in 2 Simple Steps 16

ISERROR Function helps to detect all valid excel errors are there in our worksheet, therefore ISERROR involves only one argument which is “Value”.

Now In next step, We will select the range i.e.. A1:A7 to satisfy the value argument like in below picture;

Count Errors In Excel in 2 Simple Steps 17

Now you will get the result in series of cells in bolean values i.e. TRUE (If it reverts back an excel error) or FALSE (if it’s not) as you can see below;

Count Errors In Excel in 2 Simple Steps 18

STEP2 – Count Errors Using COUNTIF Function

Now to count errors or we can say TRUE values of ISERROR Function then we can use COUNTIF Function in C1 and count the number of errors are there in worksheet.

Count Errors In Excel in 2 Simple Steps 19

Now we have two arguments of COUNTIF to satisfy, one is Range which will be B1:B7 of ISERROR results and second is criteria which will be “TRUE”

Count Errors In Excel in 2 Simple Steps 20

So now, result will be 3 and hence the answer we want to get i.e. Number of errors in excel.

Count Errors In Excel in 2 Simple Steps 21

Now, in the above process, What we have done is, we have used two different formulas i.e. ISERROR and COUNTIF to count errors in two different calculations. Excel is powered with so many functions now that we can do that in single cell.

STEP3 – Use IF Function To Return Values To 1

In B Column where we have put ISERROR function, we can use IF function to count TRUE value as 1 and FALSE as blank with “” operator and in last, we will count errors with all valid values with COUNT function.

As we know IF Function has three arguments,

First is Logical Test, Which will be the ISERROR Function itself;

Count Errors In Excel in 2 Simple Steps 22

Second is [Value_if_True] , We will put 1 to it;

Count Errors In Excel in 2 Simple Steps 23

Third is [value_if_false], We will put blank value there with “” and closes the parenthesis;

Count Errors In Excel in 2 Simple Steps 24

Here we got the result. i.e. TRUE values will be marked as 1 and others blank.

Count Errors In Excel in 2 Simple Steps 25

STEP4 – Use COUNT Function To Count Errors Values

Now, we will use COUNT function at start to count all valid values (i.e. 1) which will give us the number of errors in excel.

Count Errors In Excel in 2 Simple Steps 26

Hence, we got the result below;

Count Errors In Excel in 2 Simple Steps 27

Note: If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you.

How to insert Array Function?

Well if you have excel version prior to 2018, you need to add Array Function using the keyboard shortcut CTRL+SHIFT+ENTER after you enter any formula to the bar like below;

Count Errors In Excel in 2 Simple Steps 28

It will close the formula with {} brackets, do not enter it manually because it doesn’t work like that.

Hope you get to know how to deal with such formulas when you dont have the latest Microsoft Excel subscription or latest version, It’s very important to upgrade your excel to enhance your workbook experiences.

Learn more it’s fun :- These 6 Excel Navigation Shortcuts all you need to navigate quickly