formula to count easily

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
The Error Types return text strings, so a simple COUNTIF should work:

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

HTH,
Elkar
 
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
 
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
 
Try SUMIF:

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

Biff

driller said:
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
Click to expand...
 
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
 
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..



driller said:
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
Click to expand...
 
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

driller said:
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
Click to expand...
 
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
 
have u tested the formula when there are A-100 rows
of formula results(number,text,error)...?
Click to expand...

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
 
Back
Top