Error Count (Formula please)

[Will from London]

Hi

I am trying to come up with a formula that will count the number of errors in a range. I want the sum of all of the standard Excel errors (e.g. #DIV/0!) but also my own error messages (e.g. "#ERROR"). For the latter the first character will always be "#" and there will be no other occurences of "#" in the range.

F4 contains the range e.g. A5:Z2000

=SUMPRODUCT(--(ISERROR(INDIRECT($F$4))))
will count the number of the usual Excel errors

=SUMPRODUCT(--(LEFT(INDIRECT($F$4),1)="#"))
will count the number of occurences of my #ERROR entries it will generate an error where there are the usual Excel errors.

=SUMPRODUCT(--(ISERROR(INDIRECT($F$4)))) + SUMPRODUCT(--(LEFT(INDIRECT($F$4),1)="#"))
will display the number of #ERROR messages where there are no #DIV/0! messages but will display #DIV/0! where there are #DIV/0 errors...

Any help would be much appreciated.

Thanks

Will

 

[RoryA]

=SUMPRODUCT(ISERROR(INDIRECT($F$4))+0)+COUNTIF(INDIRECT($F$4),"#*")
should work.
Of course, it would be easier if your functions returned native errors too.

 

[Will from London]

Perfect, thank you.

(My own error messages are designed to give pointers on how to resolve them which would show up when the sheet is printed).