| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to count easily
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 -- ***** birds of the same feather flock together.. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula to count easily
The Error Types return text strings, so a simple COUNTIF should work:
=COUNTIF(A1:A100,"#DIV/0!") =COUNTIF(A1:A100,"#NAME?") etc... HTH, Elkar "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula to count easily
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 "Elkar" wrote in message ... The Error Types return text strings, so a simple COUNTIF should work: =COUNTIF(A1:A100,"#DIV/0!") =COUNTIF(A1:A100,"#NAME?") etc... HTH, Elkar "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula to count easily
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 -- ***** birds of the same feather flock together.. "Elkar" wrote: The Error Types return text strings, so a simple COUNTIF should work: =COUNTIF(A1:A100,"#DIV/0!") =COUNTIF(A1:A100,"#NAME?") etc... HTH, Elkar "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula to count easily
Try SUMIF:
=SUMIF(A1:A7,#DIV/0!,B1:B7) Biff "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. "Elkar" wrote: The Error Types return text strings, so a simple COUNTIF should work: =COUNTIF(A1:A100,"#DIV/0!") =COUNTIF(A1:A100,"#NAME?") etc... HTH, Elkar "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula to count easily
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 -- ***** birds of the same feather flock together.. "T. Valko" wrote: Try SUMIF: =SUMIF(A1:A7,#DIV/0!,B1:B7) Biff "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. "Elkar" wrote: The Error Types return text strings, so a simple COUNTIF should work: =COUNTIF(A1:A100,"#DIV/0!") =COUNTIF(A1:A100,"#NAME?") etc... HTH, Elkar "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula to count easily
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" wrote: 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 -- ***** birds of the same feather flock together.. "T. Valko" wrote: Try SUMIF: =SUMIF(A1:A7,#DIV/0!,B1:B7) Biff "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. "Elkar" wrote: The Error Types return text strings, so a simple COUNTIF should work: =COUNTIF(A1:A100,"#DIV/0!") =COUNTIF(A1:A100,"#NAME?") etc... HTH, Elkar "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula to count easily
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" wrote in message ... 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 -- ***** birds of the same feather flock together.. "T. Valko" wrote: Try SUMIF: =SUMIF(A1:A7,#DIV/0!,B1:B7) Biff "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. "Elkar" wrote: The Error Types return text strings, so a simple COUNTIF should work: =COUNTIF(A1:A100,"#DIV/0!") =COUNTIF(A1:A100,"#NAME?") etc... HTH, Elkar "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula to count easily
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 -- ***** birds of the same feather flock together.. "T. Valko" wrote: 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" wrote in message ... 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 -- ***** birds of the same feather flock together.. "T. Valko" wrote: Try SUMIF: =SUMIF(A1:A7,#DIV/0!,B1:B7) Biff "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. "Elkar" wrote: The Error Types return text strings, so a simple COUNTIF should work: =COUNTIF(A1:A100,"#DIV/0!") =COUNTIF(A1:A100,"#NAME?") etc... HTH, Elkar "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula to count easily
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:A1 0)=F1,B1:B10,#N/A))),B1:B10))) Sample file: http://cjoint.com/?gDxCpeTu6c Biff "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. "T. Valko" wrote: 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" wrote in message ... 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 -- ***** birds of the same feather flock together.. "T. Valko" wrote: Try SUMIF: =SUMIF(A1:A7,#DIV/0!,B1:B7) Biff "driller" wrote in message ... 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 -- ***** birds of the same feather flock together.. "Elkar" wrote: The Error Types return text strings, so a simple COUNTIF should work: =COUNTIF(A1:A100,"#DIV/0!") =COUNTIF(A1:A100,"#NAME?") etc... HTH, Elkar "driller" wrote: 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 -- ***** birds of the same feather flock together.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help to count easily | Excel Worksheet Functions | |||
| anyway around this, easily? | Excel Worksheet Functions | |||
| how to count easily | Excel Worksheet Functions | |||
| Can this be done easily? | Excel Discussion (Misc queries) | |||
| How to insert easily the text of a scientific formula | Excel Discussion (Misc queries) | |||
Linear Mode
