any ideas appreciated

[driller]

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*

 

[Barb Reinhardt]

I'm not exactly sure what you are asking, but try this:

=SUMPRODUCT(--(A2:A200=A1),--(B2:B200=B1),--(C2:C200=C1),--(D2200=D1))

???

HTH,
Barb Reinhardt

 

[driller]

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

 

[driller]

my coauthors now like to have it printed in different group of
colors,<cond.format> too...they now piss me with this problem...anyway better
one at a time..

regards,
driller

 

[CLR]

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]

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

 

[driller]

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

 

[T. Valko]

See your original post.

Biff

 

[driller]

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

See your original post.

Biff

 

[driller]

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

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