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.

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

## STEP1 – Detect Errors Using ISERROR Function

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

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;

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;

## 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.

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”

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

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;

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

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

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

## 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.

Hence, we got the result below;

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;

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.