Count cells that contain errors in Excel and Google Sheets

=SUMPRODUCT(--ISERROR(A2:A6))

=SUM(--ISERROR(A2:A6))

*If not Office 365 version: use "Ctrl+Shift+Enter" for both formula

A2:A6 = Ranges

Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets.

Count cells that contain errors in Excel

How to count cells that contain errors in Excel?
Count cells that contain errors in Excel

COUNT CELLS THAT CONTAIN ERRORS — EXCEL FORMULA AND EXAMPLE

=SUMPRODUCT(--ISERROR(A2:A6))

=SUM(--ISERROR(A2:A6))

💡 If not Office 365 version: use Ctrl+Shift+Enter

Count cells that contain errors in Google Sheets

How to count cells that contain errors in Google Sheets?
Count cells that contain errors in Google Sheets

COUNT CELLS THAT CONTAIN ERRORS — GOOGLE SHEETS FORMULA AND EXAMPLE

  1. =SUMPRODUCT(ISERROR(A2:A6))

  2. =ArrayFormula(SUM(--ISERROR(A2:A6)))

Other useful "COUNT FUNCTIONS: TEXT BASED CRITERIA" formulas in Excel and Google SheetsOthers FormulasDate and time functionsNumber based criteriaExtract functions