formula to count easily

[driller]

in my formulation list workbook, i have one column filled with different
formulas...
result can be a text, number, and all sorts of known error types....e.g.

#NULL!
#DIV/0!
#VALUE!
#REF!
#NAME?
#NUM!
#N/A

I like to count (A1:A100) how many formula are bearing each error.type...
this may enlighten me how to clean the list of formula bearing correct
results...

pls. bear with me...

regards,
driller

 

[Elkar]

The Error Types return text strings, so a simple COUNTIF should work:

=COUNTIF(A1:A100,"#DIV/0!")
=COUNTIF(A1:A100,"#NAME?")
etc...

HTH,
Elkar

 

[T. Valko]

The Error Types return text strings

They're not text strings but I don't know the exact terminology. They're not
logicals or booleans but I call them logicals even though Excel defines
logical values as either TRUE ot FALSE.

A1 = #DIV/0!

=ISTEXT(A1) = FALSE

=COUNTIF(A1:A100,"#DIV/0!")
=COUNTIF(A1:A100,#DIV/0!)

Both work.

Biff

 

[driller]

thanks for ur kind reply,

the purpose actually is to count how many times an error exist in a formula
whwn multiplied to my column (B1:B100).
Note: Column (B1:B100) contains the number of times a certain cell formula
from column A had been utilized in the workbook of 2 sheets.

i'm thinking to use sum or sumproduct yet i find hard time for some error
type counting (e.g. #Ref!, etc)...

Purpose : to distinguish those formulas in the workbook that has to be
revised by my co-authors and by how may times for each author assigned for
each errot type correction..

thanks if someone will help me other than from my co-authors listed in
column(C1:C100)

been dribled with my regards,
driller

 

[T. Valko]

Try SUMIF:

=SUMIF(A1:A7,#DIV/0!,B1:B7)

Biff

thanks for ur kind reply,

the purpose actually is to count how many times an error exist in a
formula
whwn multiplied to my column (B1:B100).
Note: Column (B1:B100) contains the number of times a certain cell formula
from column A had been utilized in the workbook of 2 sheets.

i'm thinking to use sum or sumproduct yet i find hard time for some error
type counting (e.g. #Ref!, etc)...

Purpose : to distinguish those formulas in the workbook that has to be
revised by my co-authors and by how may times for each author assigned for
each errot type correction..

thanks if someone will help me other than from my co-authors listed in
column(C1:C100)

been dribled with my regards,
driller

 

[driller]

thanks for quick reply...
I need to have the other summing if criteria (c1:c100="Author Name").
to summarize the error checking tasks of my co-authorses...

regards,
driller

 

[driller]

i hope someone from our group can formula-count this easily with criteria....

thanks anyway for trying...
regards,
driller
--
*****
birds of the same feather flock together..

thanks for quick reply...
I need to have the other summing if criteria (c1:c100="Author Name").
to summarize the error checking tasks of my co-authorses...

regards,
driller

 

[T. Valko]

Your English is not easy to understand. I think this is what you want:

A1:A7 = errors returned by formulas
B1:B7 = numeric values
C1:C7 = names

=SUMPRODUCT(--(ERROR.TYPE(A1:A7)=n),B1:B7,--(C1:C7="name"))

Where n = a number from 1 to 7. See help on ERROR.TYPE for those numbers.

Biff

thanks for quick reply...
I need to have the other summing if criteria (c1:c100="Author Name").
to summarize the error checking tasks of my co-authorses...

regards,
driller

 

[driller]

Sir Valko,
thanks, have u tested the formula when there are A-100 rows of formula
results(number,text,error)...?

i tried to test the sp formula just as u told me...yet i dont know why
error.type reads a non-error into an error...is this a circular logic..
formula do not work when i do "error.type(A1:A100)" in sp..

regards,
driller

 

[T. Valko]

have u tested the formula when there are A-100 rows

of formula results(number,text,error)...?

No. In your original post you didn't say anything about having numbers or
text. Try this array formula**:

A1:A10 = numbers, text, empty cells, formula blanks, error values
B1:B10 = numbers
C1:C10 = text
F1 = a number from 1 to 7, the error.type

=SUM(IF(C1:C10="x",IF(NOT(ISNA(IF(ERROR.TYPE(A1:A10)=F1,B1:B10,#N/A))),B1:B10)))

Sample file:

http://cjoint.com/?gDxCpeTu6c

Biff