Count Errors in a Range

Excel Formulas › Information

All versionsISERROR

Quickly audit a sheet by counting how many cells contain errors. SUMPRODUCT over ISERROR tallies every #N/A, #DIV/0!, and #VALUE! in one formula — no helper column.

Quick formula: count error cells in B2:B100:
=SUMPRODUCT(--ISERROR(B2:B100))
ISERROR returns TRUE/FALSE per cell; the double-negative turns them to 1/0 for SUMPRODUCT to add.

Functions used (tap for the full reference guide):

ISERRORSUMPRODUCT

The example

Three of these cells are broken.

AB
1ValueErrors
2#N/A3
3#DIV/0!

The formula

The formula:

=SUMPRODUCT(--ISERROR(B2:B100)) // counts all error types

How it works

How it works:

  1. ISERROR(range) returns a TRUE/FALSE array marking every error cell.
  2. The double unary -- coerces TRUE/FALSE to 1/0.
  3. SUMPRODUCT adds the 1s — the total number of errors.
  4. In Excel 365 you can also use =SUM(--ISERROR(range)) entered normally, since arrays spill automatically.

Count a specific error type: =SUMPRODUCT(--(ERROR.TYPE(range)=7)) counts only #N/A (error code 7), and --ISNA(range) does the same more simply. ISERROR catches all errors; ISERR catches all except #N/A.

Try it: interactive demo

Live demo

Cells (type words; "err" marks an error).

Errors:

Variations

Count #N/A only

Missing-lookup count:

=SUMPRODUCT(--ISNA(B2:B100))

Any error? (yes/no)

Flag the column:

=IF(SUMPRODUCT(--ISERROR(B2:B100))>0,"Check","OK")

365 spill

Plain SUM:

=SUM(--ISERROR(B2:B100))

Pitfalls & errors

ISERROR vs ISERR. ISERROR counts #N/A too; ISERR skips it — pick based on whether #N/A counts.

The -- is required. Without coercion, SUMPRODUCT adds TRUE/FALSE as text and returns 0.

Whole-column ranges are slow. Limit the range to the data for big sheets.

Practice workbook

📊
Download the free Count Errors in a Range practice workbook
A count-errors sheet with the #N/A-only, flag, and 365-spill variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count error cells in a range in Excel?
Use =SUMPRODUCT(--ISERROR(range)). ISERROR flags each error TRUE, the -- turns them to 1/0, and SUMPRODUCT adds them.
How do I count only #N/A errors?
Use =SUMPRODUCT(--ISNA(range)), which counts just #N/A cells.
What's the difference between ISERROR and ISERR?
ISERROR treats #N/A as an error; ISERR ignores #N/A and flags all other error types.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Check if error · Highlight errors · IFERROR handling

Function references: ISERROR · SUMPRODUCT