any ideas appreciated

D

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*
 
B

Barb Reinhardt

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
 
D

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
 
D

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
 
C

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
 
D

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
 
D

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
 
D

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



T. Valko said:
See your original post.

Biff
 
D

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



driller said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell Counter/Increment 2
Summarizing Data 1
comment copy 3
IF formulas 5
LOOKUP, MATCH MORE MATCHES IN TWO COLUMN 1
sumif 1
Lookup Functions 3
populating multiple cells 1

Top