any ideas appreciated

any ideas appreciated

Post by ZHJpbGxlc » Sun, 01 Jul 2007 00:24:03


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..
 
 
 

any ideas appreciated

Post by QmFyYiBSZW » Sun, 01 Jul 2007 00:56:01

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

 
 
 

any ideas appreciated

Post by ZHJpbGxlc » Sun, 01 Jul 2007 01:16:02

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..
 
 
 

any ideas appreciated

Post by ZHJpbGxlc » Sun, 01 Jul 2007 01:26:02

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..
 
 
 

any ideas appreciated

Post by Q0xS » Sun, 01 Jul 2007 01:54:01

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
 
 
 

any ideas appreciated

Post by ZHJpbGxlc » Sun, 01 Jul 2007 02:16:05

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..
 
 
 

any ideas appreciated

Post by ZHJpbGxlc » Sun, 01 Jul 2007 05:30:00

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..
 
 
 

any ideas appreciated

Post by T. Valk » Sun, 01 Jul 2007 06:31:37

See your original post.

Biff
 
 
 

any ideas appreciated

Post by ZHJpbGxlc » Sun, 01 Jul 2007 07:16:01

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..
 
 
 

any ideas appreciated

Post by ZHJpbGxlc » Sun, 01 Jul 2007 07:38:00


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..