Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • To count total rows in a range in Excel
  • Left Lookup in Excel
  • How to create dynamic named range with OFFSET in Excel
  • Count unique text values with criteria

Data Analysis Examples

  • Conditional Formatting New Rule with Formulas in Excel
  • How to Create Area Chart in Excel
  • Conditional Formatting Rules in Excel
  • Create Scatter Chart in Excel
  • How to add Trendline to a chart in Excel

Data Validation Examples

  • Excel Data validation date in next 30 days
  • How To Create Drop-down List in Excel
  • Excel Data validation with conditional list
  • Excel Data validation date in specific year
  • Excel Data validation require unique number

Count cells that contain errors in Excel

by

This tutorial shows how to Count cells that contain errors in Excel using the example below;

Formula

=SUMPRODUCT(–ISERR(range))

Explanation

To count cells that contain errors, you can use the ISERR function, wrapped in the SUMPRODUCT function.  In the example shown, E5 cell contains this formula:

=SUMPRODUCT(--ISERROR(B5:B9))

How this formula works

SUMPRODUCT accepts one or more arrays, multiplies the arrays together, and returns the “sum of products” as a final result. If only one array is supplied, SUMPRODUCT simply returns the sum of items in the array.

Worked Example:   Find, Trace and Correct Errors in Excel Formulas using 'Formula Auditing'

The ISERROR function returns TRUE when a cell contains an error, and FALSE if not. In the example, ISERROR receives a range of cells (B5:B9) as input. Because there are five cells in the range, ISERROR evaluates each cell and returns five results in an array of TRUE / FALSE values:

{TRUE;FALSE;TRUE;FALSE;FALSE}

To coerce the TRUE/FALSE values to 1’s and 0’s, we use a double negative (called a double unary). The resulting array looks like this:

{1;0;1;0;0}

Finally, SUMPRODUCT sums the items in this array and returns the total, which is 3 in this case.

Worked Example:   Count visible rows only with criteria in Excel

Note: ISERROR counts all errors. If for some reason you want to count all errors except #N/A, you can use the ISERR function instead.

Worked Example:   Exact match lookup with SUMPRODUCT in Excel

Array formula with SUM

You can also use the SUM function to count errors, but you must enter as an array formula using control + shift + enter. Once entered the formula will look like this:

{=SUM(--ISERROR(range))}

The curly brackets are added automatically by Excel and indicate an array formula.

Post navigation

Previous Post:

Count cells that contain odd numbers in Excel

Next Post:

Count cells not equal to x or y in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Extract middle name from full name — Manipulating NAMES in Excel
  • How to find nth occurrence of character in Excel
  • How to translate letters to numbers in Excel
  • How to check if cell contains all of many things in Excel
  • Remove unwanted characters in Excel
  • Convert date to text in Excel
  • Get last working day in month in Excel
  • Get first Monday before any date in Excel
  • Custom weekday abbreviation in Excel
  • Steps to create Dynamic calendar grid in Excel
  • NPER function: Description, Usage, Syntax, Examples and Explanation
  • Calculate loan interest in given year in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • How to Calculate Tax Rates in Excel
  • How to calculate principal for given period in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles