| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
any ideas appreciated
Hello,
Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
any ideas appreciated
I'm not exactly sure what you are asking, but try this:
=SUMPRODUCT(--(A2:A200=A1),--(B2:B200=B1),--(C2:C200=C1),--(D2:D200=D1)) ??? HTH, Barb Reinhardt "driller" wrote: Hello, Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
any ideas appreciated
i've tried kinda like this...
=SUMPRODUCT(--(error.type(A2:A200)=B1),--(C2:C200=A1),(B2:B200)) i got #N/A, B1 lists error.types numbered from 1 to 7... i do the sum*array* formula also but gave me same results that must not be printed and should not contain blanks... I dont know now how much time and accuracy i need to count by hand these auditing figures and coordinate/share/detect/correct the necessary formulas from my colleagues to allow us all print a simultaneously linked-final summary report. i hope you can bear with me on this basic counting procedure problem... The formula results on col A, is the major reference of our auditing excel guru who dont like error messages..i mean error results. I hope i can tackle this alone, but really not possible without the supporting group on this forum.. with deep regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'm not exactly sure what you are asking, but try this: =SUMPRODUCT(--(A2:A200=A1),--(B2:B200=B1),--(C2:C200=C1),--(D2:D200=D1)) ??? HTH, Barb Reinhardt "driller" wrote: Hello, Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
any ideas appreciated
my coauthors now like to have it printed in different group of
colors,<cond.format too...they now **** me with this problem...anyway better one at a time.. regards, driller -- ***** birds of the same feather flock together.. "driller" wrote: i've tried kinda like this... =SUMPRODUCT(--(error.type(A2:A200)=B1),--(C2:C200=A1),(B2:B200)) i got #N/A, B1 lists error.types numbered from 1 to 7... i do the sum*array* formula also but gave me same results that must not be printed and should not contain blanks... I dont know now how much time and accuracy i need to count by hand these auditing figures and coordinate/share/detect/correct the necessary formulas from my colleagues to allow us all print a simultaneously linked-final summary report. i hope you can bear with me on this basic counting procedure problem... The formula results on col A, is the major reference of our auditing excel guru who dont like error messages..i mean error results. I hope i can tackle this alone, but really not possible without the supporting group on this forum.. with deep regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'm not exactly sure what you are asking, but try this: =SUMPRODUCT(--(A2:A200=A1),--(B2:B200=B1),--(C2:C200=C1),--(D2:D200=D1)) ??? HTH, Barb Reinhardt "driller" wrote: Hello, Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
any ideas appreciated
Maybe.........
=IF(ISERROR(A1),"ErrorType " & B1&" ("& LOOKUP(B1,{1,2,3,4,5,6,7},{"#NULL!","#DIV/O!","#VALUE!","#REF!","#NAME?","#NUM!","#N/A"}) & ")","") Vaya con Dios, Chuck, CABGx3 "driller" wrote: Hello, Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
any ideas appreciated
sorry for unclear english
----------- Row 1 A1 = "name" to be typed and must matched with C2:C100. B1 = entry either from number 1 up to 7 : and shall be matched with ERROR.TYPE(A2:A100) C1 = to contain the formula we need to sum B2:B100 based on above criteria ----------- *****A2:A100 contains numbers, text, error_result like{"#NULL!","#DIV/O!","#VALUE!","#REF!","#NAME?","#NUM!","#N/A"} *****B2:B100 contains numbers <sum_range *****C2:C100 contains text <name_range --------------------|thanks... regards, driller -- ***** birds of the same feather flock together.. "CLR" wrote: Maybe......... =IF(ISERROR(A1),"ErrorType " & B1&" ("& LOOKUP(B1,{1,2,3,4,5,6,7},{"#NULL!","#DIV/O!","#VALUE!","#REF!","#NAME?","#NUM!","#N/A"}) & ")","") Vaya con Dios, Chuck, CABGx3 "driller" wrote: Hello, Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
any ideas appreciated
thanks for all the related posts, i arrived with a sum or sp with cse that
does my first problem...after some few test only... regards, driller -- ***** birds of the same feather flock together.. "CLR" wrote: Maybe......... =IF(ISERROR(A1),"ErrorType " & B1&" ("& LOOKUP(B1,{1,2,3,4,5,6,7},{"#NULL!","#DIV/O!","#VALUE!","#REF!","#NAME?","#NUM!","#N/A"}) & ")","") Vaya con Dios, Chuck, CABGx3 "driller" wrote: Hello, Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
any ideas appreciated
See your original post.
Biff "driller" wrote in message ... Hello, Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
any ideas appreciated
here's the formula...a bit longer than a simple one...
To anyone, please help me to simplify **--| on C1 {=SUM(($B$2:$B$23)*($C$2:$C$23=A1)*(($B$1&"-ERR")=IF(ISERROR($A$2:$A$23),ERROR.TYPE($A$2:$A$23 )&"-ERR",$A$2:$A$23)))} =SUMPRODUCT(($B$2:$B$23)*($C$2:$C$23=A1)*(($B$1&"-ERR")=IF(ISERROR($A$2:$A$23),ERROR.TYPE($A$2:$A$23 )&"-ERR",$A$2:$A$23))) regards, driller -- ***** birds of the same feather flock together.. "T. Valko" wrote: See your original post. Biff "driller" wrote in message ... Hello, Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
any ideas appreciated
i have just realized the importance of the paragraphic formula like this one..... if(array_criteria,array_result)... that works in counting and summing...with CSE.. and to make the "formula does work" for sumproduct and sum array formulas... ***unless other varying function/formula can be used for the same purpose...*** with good luck fix with regards, driller -- ***** birds of the same feather flock together.. "driller" wrote: here's the formula...a bit longer than a simple one... To anyone, please help me to simplify **--| on C1 {=SUM(($B$2:$B$23)*($C$2:$C$23=A1)*(($B$1&"-ERR")=IF(ISERROR($A$2:$A$23),ERROR.TYPE($A$2:$A$23 )&"-ERR",$A$2:$A$23)))} =SUMPRODUCT(($B$2:$B$23)*($C$2:$C$23=A1)*(($B$1&"-ERR")=IF(ISERROR($A$2:$A$23),ERROR.TYPE($A$2:$A$23 )&"-ERR",$A$2:$A$23))) regards, driller -- ***** birds of the same feather flock together.. "T. Valko" wrote: See your original post. Biff "driller" wrote in message ... Hello, Ive been block out for this one task of checking results from the huge workbooks from different departments...full of stress and pressure... here's my data from Row 2.. COL A COL B COL C COL D Results as data numbers names =ISerror(ERROR.TYPE(data)) #N/A 1 AA FALSE ggg 1 BB TRUE #N/A 1 CC FALSE #NULL! 1 AA FALSE #NULL! 1 BB FALSE #REF! 1 CC FALSE 2 1 AA TRUE #REF! 1 BB FALSE A100 1 CC TRUE #NUM! 1 AA FALSE #NUM! 1 BB FALSE 1 CC TRUE 1 1 AA TRUE #NUM! 1 BB FALSE #DIV/0! 1 CC FALSE #DIV/0! 1 AA FALSE #VALUE! 1 BB FALSE 0 1 CC TRUE #VALUE! 1 AA FALSE #NAME? 1 BB FALSE -1 1 CC TRUE #NAME? 1 AA FALSE -------------- may someone share a simple formula lets say on A1 I will type a name e.g. "AA", and then on B1 i may type the Error.type (1 to 7 only), and then on C1 i need your formula to bear the sum of Col B *numbers* as per kinda criteria of "col A= A1", and "col B=B1". regards with kindness, driller not *killer* -- ***** birds of the same feather flock together.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help would be much appreciated! | Excel Worksheet Functions | |||
| Any help much appreciated | Excel Discussion (Misc queries) | |||
| Re-Post - Any Help Appreciated | Excel Worksheet Functions | |||
| TAN different to a calculator? Please help would be appreciated lo | Excel Worksheet Functions | |||
| Hello, again Help is appreciated | Excel Worksheet Functions | |||
Linear Mode
