Explanation
In this example, the goal is to count errors in the range B5:B15, which is named data for convenience. The article below explains several different approaches, depending on your needs. For background, this article: Excel Formula Errors .
COUNTIF function
One way to count individual errors is with the COUNTIF function like this:
=COUNTIF(data,"#N/A") // returns 1
=COUNTIF(data,"#VALUE!") // returns 1
=COUNTIF(data,"#DIV/0!") // returns 0
This is an odd syntax since technically errors are not text values . But COUNTIF is in a group of eight functions that have some quirks , and this is one of them. During calculation, COUNTIF is able to resolve the text into the given error and return a count of that error. One limitation of this approach is that there is no simple way to count all error types with a single formula. You might think we can use COUNTIF like this:
=COUNTIF(ISERROR(data),TRUE) // fails
The idea here is that the ISERROR function will return TRUE or FALSE, and COUNTIF will count the TRUE results. However, if you try to enter this formula, Excel won’t let you. This is a case where COUNTIF won’t work because it won’t allow an " array operation " in place of a normal range. One solution is to use SUMPRODUCT with ISERROR, as explained below.
SUMPRODUCT function
A better way to count errors in a range is to use the SUMPRODUCT function with the ISERROR function and Boolean logic . The SUMPRODUCT function accepts one or more arrays, multiplies the arrays together, and returns the “sum of products” as a final result. If only one array is supplied, SUMPRODUCT simply returns the sum of items in the array. In the example shown, the formula in E6 is:
=SUMPRODUCT(--ISERROR(data))
Working from the inside out, the ISERROR function returns TRUE when a cell contains an error, and FALSE if not. Because there are eleven cells in the range B5:B15, ISERROR evaluates each cell and returns 11 results in an array like this:
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
Next, we use a double negative (–) to convert the TRUE and FALSE values to 1s and 0s. The resulting array inside the SUMPRODUCT function looks like this:
=SUMPRODUCT({0;0;0;1;0;0;0;1;0;0;0})
Finally, SUMPRODUCT sums the items in this array and returns the total, which is 2 in this case.
ISERR option
=SUMPRODUCT(--ISERR(data)) // returns 1
Since one of the errors shown in the example is #N/A, this formula returns 1 instead of 2.
Count by error code
Each Excel formula error is associated with a numeric error code ( complete list here ). You can retrieve this code with the ERROR.TYPE function . To count errors by numeric code, you can use ERROR.TYPE instead of ISERROR. For example to count #VALUE! errors, which have a numeric code of 3, you can use a formula like this:
=SUMPRODUCT(--(IFERROR(ERROR.TYPE(data),0)=3))
In an ironic twist, we need the IFERROR function to help us here. The ERROR.TYPE function will return a numeric code for any formula error. However, if a cell does not contain an error, ERROR.TYPE itself returns an #N/A error, which will bubble to the top and cause the entire formula to return #N/A. We use IFERROR to map the #N/A errors to zero so that we can compare the results from ERROR.TYPE to 3 without trouble. This part of the formula evaluates like this:
=IFERROR(ERROR.TYPE(data),0)=3)
=IFERROR({#N/A;#N/A;#N/A;3;#N/A;#N/A;#N/A;7;#N/A;#N/A;#N/A},0)=3)
={0;0;0;3;0;0;0;7;0;0;0}=3
={FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
Notice in the second step above, we see error code 3 and error code 7. This indicates the #VALUE! and #N/A error in data (B5:B15). After IFERROR runs, the #N/A errors are gone and replaced with zeros, as you can see in the third step. This lets us check for error code 3 without trouble. From there, we use a double negative to convert the TRUE and FALSE values to 1s and 0s and the resulting array is returned directly to SUMPRODUCT:
=SUMPRODUCT({0;0;0;1;0;0;0;0;0;0;0}) // returns 1
The final result is 1, since there is one error in data with an error code of 3.
Note: In Excel 365 and Excel 2021 you can use the SUM function instead of SUMPRODUCT in all formulas above if you prefer, with some caveats. This article provides a complete explanation .
Explanation
In this example, the goal is to count the number of cells in B5:B15 that contain a given number of characters, where the number of characters n is provided as a variable in cell E4.
SUMPRODUCT with LEN
One way to solve this problem is to use the SUMPRODUCT function with the LEN function . In the example shown, the formula in E6 is:
=SUMPRODUCT(--(LEN(B5:B15)=E4))
Working from the inside out, the LEN function is used to get the length of each value in the range like this:
LEN(B5:B15)
Since the range B5:B15 contains 11 cells, LEN returns 11 results in an array like this:
{5;4;5;6;5;4;6;6;5;4;7}
Each number in the array is the length of a cell in B5:B15. This array is then compared to cell E4, which contains the number 5. The result is a new array containing 11 TRUE and FALSE values. To summarize:
=LEN(B5:B15)=E4
={5;4;5;6;5;4;6;6;5;4;7}=5
={TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
Each TRUE value corresponds to a cell in B5:B15 that contains 5 characters.
Next, a double-negative (–) is used to convert the TRUE and FALSE values to 1s and 0s, and the resulting array is delivered directly to the SUMPRODUCT function:
=SUMPRODUCT({1;0;1;0;1;0;0;0;1;0;0}) // returns 4
SUMPRODUCT returns the sum of the array, 4, as a final result. If a new number is entered in cell E4, the formula will recalculate and return a new result.
COUNTIF function
Another way to solve this problem is with the COUNTIF function and the question mark (?) wildcard . COUNTIF supports three wildcards that can be used in the criteria argument : question mark (?), asterisk(), or tilde (~). A question mark (?) matches any one character and an asterisk () matches zero or more characters of any kind. In this example, we can use the question mark (?) wildcard to count cells that contain 5 characters like this:
=COUNTIF(B5:B15,"?????") // returns 4
The “?” symbol is a wildcard in Excel that means “match any single character”, so this pattern will count cells that contain five characters. To adapt the formula above to use n from cell E4, we can add the REPT function like this:
=COUNTIF(B5:B15,REPT("?",E4))
The REPT function repeats the “?” five times inside COUNTIF, so the result is the same.
Note: One difference in the COUNTIF formula is that COUNTIF with “?” as a wildcard will only count characters in text values — cells that contain numeric values will not be counted. The SUMPRODUCT + LEN formula on the other hand will count characters of any kind, including numbers.
