How to count cells that contain errors
✓ Verified in LibreOffice 25.8.7.3Tally how many cells in a range are #N/A, #DIV/0!, #VALUE! etc. — a quick data-quality check.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =SUMPRODUCT(--ISERROR(A2:A6)) | The -- turns TRUE/FALSE into 1/0. Count only #N/A with ISNA instead of ISERROR. |
| Google Sheets | =SUMPRODUCT(--ISERROR(A2:A6)) | Identical; =COUNTIF(A2:A6,"#N/A") also works for just #N/A. |
| LibreOffice Calc | =SUMPRODUCT(--ISERROR(A2:A6)) | Identical. |
How it works
ISERROR returns TRUE for any error cell, the double-negative -- converts the TRUE/FALSE array to 1s and 0s, and SUMPRODUCT totals them — 2 error cells here (the #DIV/0! and the #N/A). A plain COUNTIF can't do this because it can't reliably match error values across all apps. Swap ISERROR for ISNA to count only lookup misses (#N/A), or ISERR to count everything EXCEPT #N/A. This is the fast way to audit a column before trusting a total.
Verified, not just documented
We ran =SUMPRODUCT(--ISERROR(A2:A6)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 2 — exactly the expected result. Every formula here is confirmed by actually executing it.