Archive of Mr Excel Message Board

Hi JAF

Just off the tiop of my head you could run a simple formula like: =Type(A1) down a corresponding column then use a simple:
=COUNTIF(A1:A100,16)

I i get chance later I'll have a play with an array. or better still the DCOUNT ?

Dave
OzGrid Business Applications

Thanks for that Dave.

I'd already got the result using the extra column using an ISERROR formula and then COUNTIF any instances of TRUE (same result as yours, slightly different method).

If possible I want to avoid the extra column and do it all in an array formula.

I'll leave it to your Aussie genius to have a play with! :-))

JAF : Hiya : I've been trying to work out a formula that will look at a range (say D2:D2000) and count the number of cells that contains errors (#DIV0, #NA etc.) : I've tried various permutations of count and countif in array formulas, but can't get it to work. : I'm sure this is one of those obvious bits of logic, but I just can't get my head round it. : Any help appreciated. :

I'd already got the result using the extra column using an ISERROR formula and then COUNTIF any instances of TRUE (same result as yours, slightly different method). If possible I want to avoid the extra column and do it all in an array formula. I'll leave it to your Aussie genius to have a play with! :-))

JAF, as I'm not a huge fan of arrays (in most cases) you could use the DCOUNT.

Lets say your range you want to count the errors in is B2:B500.
In D1 paste the column heading for B2:B500
Then below this copied heading put all the error types you want to count eg; #N/A, #DIV/0!, #VALUE!, #REF!, #NUM!, #NAME! and #NUM!

Then use:
=DCOUNTA(B1:B500,B1,D1:D8)

The good part of this is you can easily add/take error types, and the database functions wont bog down recalculation like too many arrays can.

Dave

OzGrid Business Applications

Excellent Dave

That works nicely - thanks.

JAF

Alternatively:

Array-enter:

=SUM((0+ISERROR(D2:D2000)))

Aladin