Count Number of Cell with Errors in Excel

This problem occurs, when you have a column with a formula that sometimes returns an error.  That is very typical for all kind of look-up situations .  Let’s say, you have a column E with formula that returns good values and errors.  You need to count how many errors are in that column.

Enter formula

=SUM(IF(ISERROR(E2:E10),1))

and press Ctrl+Shift+Enter while still in editing mode.
You should see two things.  First, your formula turns into

{=SUM(IF(ISERROR(E2:E10),1))}

with square brackets around it, and second, you got you a correct cells with errors count.  This is it.

If you need to count cell without errors, you can use this formula:

=SUM(IF( NOT( ISERROR(E2:E10)),1 ))

and press Ctrl+Shift+Enter while in editing mode.

You can further experiment with this solution.
For example, try using:

=COUNTIF(E2:E10,"=#N/A")

I found that this formula works, if your errors happened to be of type #N/A .

Note: above method tested in Excel 2010, 2013 and Excel 365 [2017-06-15 TH]

In case you need to count non-blank cells in a range use this formula:

=COUNTA(E2:E10)

Formula above allow for multiple non-continuous ranges.

 

To go to the bottom of data and not to the bottom of the sheet, use  End and Home (not tested yet) instead of usual Ctrl + End

(Visited 10 times, 1 visits today)

Be the first to comment

Your question, correction or clarification Ваш вопрос, поправка или уточнение

Editor on duty will review your comment. Can't wait? Discuss your topic right now in forums, if you register and login.     Required fields are marked with * Ваше послание пойдет на просмотр в редакцию. Не можете ждать? Обсудите ваш вопрос прямо сейчас в форуме Русский Круг , ecли вы зарегистрируетесь и войдёте под своим именем.     Поля, обязательные для заполнения, помечены *
* *