# How to Count Non-Error Cells in Excel

When working with large data sets, it is not uncommon to encounter errors in your data, such as #DIV/0!, #REF!, or #VALUE!. These errors can be problematic when you want to perform calculations or analysis on your data, as they can cause incorrect results or even crashes. Therefore, it is often useful to be able to count the number of cells in a worksheet that do not contain errors, so that you can focus on the valid data.

## Using the ISERROR Function

The ISERROR function is a simple function that returns TRUE if a cell contains any type of error, and FALSE otherwise. To count the number of cells in a worksheet that do not contain errors, you can use the NOT function in combination with the ISERROR function.

For example, suppose you have a data set in cells A1:A10, and you want to count the number of cells that do not contain errors. You can use the following formula:

``=COUNTIF(NOT(ISERROR(A1:A10))),TRUE))``

This formula uses the NOT function to reverse the result of the ISERROR function, so that it returns TRUE for non-error cells, and FALSE for error cells. The COUNTIF function then counts the number of TRUE values in the range.

## Using the NOT Function

The NOT function is a logical function that returns the opposite of a given logical value. For example, if a cell contains the logical value TRUE, the NOT function will return FALSE, and vice versa.

In the example above, we used the NOT function in combination with the ISERROR function to count the number of non-error cells in a range. But the NOT function can also be used on its own to count cells that meet a specific criterion.

For example, suppose you have a data set in cells A1:A10, and you want to count the number of cells that contain the text “apple”. You can use the following formula:

``=COUNTIF(NOT(ISERROR(A1:A10))),TRUE))``

This formula uses the NOT function to reverse the result of the ISERROR function, so that it returns TRUE for cells that contain the text “apple”, and FALSE for cells that do not. The COUNTIF function then counts the number of TRUE values in the range.

## Using the SUMPRODUCT Function

The SUMPRODUCT function is a versatile function that can be used to perform a variety of calculations on arrays of data. In the context of counting non-error cells in Excel, the SUMPRODUCT function can be used to count the number of cells that meet multiple criteria.

For example, suppose you have a data set in cells A1:B10, and you want to count the number of cells that do not contain errors and also contain the text “apple” in column A. You can use the following formula:

``=SUMPRODUCT(--(NOT(ISERROR(A1:A10))),--(A1:A10="apple"))``

This formula uses the NOT function to reverse the result of the ISERROR function, so that it returns TRUE for non-error cells, and FALSE for error cells. The — operator is used to convert the TRUE and FALSE values to 1’s and 0’s, respectively. The formula then multiplies these two arrays of 1’s and 0’s, resulting in a new array of 1’s and 0’s where 1 indicates that the corresponding cell meets both criteria. Finally, the SUMPRODUCT function adds up the values in this new array, giving the total count of cells that meet both criteria.

## Using the COUNTIF Function

The COUNTIF function is a powerful function that can count the number of cells in a range that meet a specific criterion. To count the number of cells that do not contain errors, you can use the NOT function in combination with the ISERROR or ISERR function, as shown in the previous examples.

For example, suppose you have a data set in cells A1:A10, and you want to count the number of cells that do not contain errors. You can use the following formula:

``=COUNTIF(NOT(ISERROR(A1:A10))),TRUE))``

This formula uses the same approach as in the first example, using the NOT function to reverse the result of the ISERROR function, so that it returns TRUE for non-error cells, and FALSE for error cells. The COUNTIF function then counts the number of TRUE values in the range.

## Combining Functions for More Complex Criteria

In some cases, you may need to count cells that meet multiple criteria that cannot be handled by a single function. In these cases, you can combine multiple functions using logical operators such as AND and OR.

For example, suppose you have a data set in cells A1:B10, and you want to count the number of cells that do not contain errors and also contain the text “apple” in column A or “orange” in column B. You can use the following formula:

``=SUMPRODUCT(--(NOT(ISERROR(A1:A10))),--((A1:A10="apple")+(B1:B10="orange")))``

This formula uses the same approach as in the SUMPRODUCT example, using the NOT function to reverse the result of the ISERROR function, and the — operator to convert the TRUE and FALSE values to 1’s and 0’s. The formula then uses the addition operator to combine two arrays of 1’s and 0’s, resulting in a new array of 1’s and 0’s where 1 indicates that the corresponding cell meets either criteria. Finally, the SUMPRODUCT function adds up the values in this new array, giving the total count of cells that meet either criteria.

## Bibek Sapkota

I'm Bibek | Tech Enthusiast & Lifelong Learner. | Playing on the Web for the Past Few Years as an SEO Specialist and Full-Time Blogger. I'm constantly seeking out new opportunities to learn and grow, and I love sharing my knowledge with others. This is where I started this blog! Here, you will find me sharing comprehensive reviews, helpful guides, tips-tricks and ways to get the full benefits of evolving technology. On this blog, you can also explore Powerful Knowledge, Tips & Resources On Blogging, SEO and Passive income Opportunities.