any ideas appreciated

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

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

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

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

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

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

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

Biff

any ideas appreciated

here's the formula...a bit longer than a simple one...

To anyone,

**--|>

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

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