Count cells that do not have errors
This tutorial shows how to count the number of cells in a specified range that do not contain errors using an Excel formula, with the SUMPRODUCT, NOT and ISERROR functions
|
=SUMPRODUCT(--NOT(ISERROR(B5:B9)))
|
GENERIC FORMULA
=SUMPRODUCT(--NOT(ISERROR(B5:B9)))
ARGUMENTS EXPLANATION For each cell in the selected range, the combination of the NOT and ISERROR functions identifies the cells that do not contain an error. Then the SUMPRODUCT function is used to sum the number of cells that the NOT and ISERROR functions identified as not containing an error.
In this example the range from which we are counting the total number of cells that do not contain an error is B5:B9. The formula returns a value of 2 as the cells within that range that do not contain an error are B5 and B8. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count cells with errors | How to count the number of cells in a specified range that contain errors |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values | |
| NOT Function | The Excel NOT function returns the opposite result of a logical value |