Can I Spreadsheet?
← All how-to recipes

How to count cells that contain errors

✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)

Tally how many cells in a range are #N/A, #DIV/0!, #VALUE! etc. — a quick data-quality check.

The formula

AppFormulaNotes
Excel (desktop)=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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 2 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

SUMPRODUCT · ISERROR — see full Excel, Google Sheets & LibreOffice compatibility for each.

Related recipes

Related comparisons