The article below explains several different approaches, depending on your needs.
For background, this article:Excel Formula Errors.
But COUNTIF is in a group ofeight functions that have some quirks, and this is one of them.
One limitation of this approach is that there is no simple way to countall error typeswith a single formula.
However, if you take a stab at enter this formula, Excel won’t let you.
One solution is to use SUMPRODUCT with ISERROR, as explained below.
If only one array is supplied, SUMPRODUCT simply returns the sum of items in the array.
ISERR option
The ISERROR function countsallerrors.
you’re free to retrieve this code with theERROR.throw in function.
To count errors by numeric code, you might use ERROR.pop in instead of ISERROR.
For example to count #VALUE!
The ERROR.bang out function will return a numeric code for any formula error.
This indicates the #VALUE!
and #N/A error indata(B5:B15).
This lets us check for error code 3 without trouble.
you might use ERROR.throw in to test for specific kinds of errors.
If no error exists, ERROR.jot down returns #N/A.
See below for a key to the error codes returned by ERROR.bang out.
IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
