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

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

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

???

HTH,

Barb Reinhardt

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

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

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

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

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

=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

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

-----------

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

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

does my first problem...after some few test only...

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

--

*****

birds of the same feather flock together..

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

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

1. DSL Install Fiasco... Any Ideas Appreciated!

3. Vague question I know, but any idea appreciated (Maybe Flash expert needed)

4. Easier navigation? Ideas appreciated

5. Online Events Calendar -- Help, ideas, appreciated

6. Monitor won`t go to sleep Help please Stll have problem: Ideas appreciated.

7. Best algorithm for sorting a linked list (of game enemys)? ideas appreciated! :-)

8. Best algorithm for sorting a linked list (of game enemys)? ideas appreciated! :-)

9. any idea? any help would be greatly appreciated

10. Plz HELP check codes before Viva next week!!!Your ideas are much appreciated...!

11. Help (ideas) greatley appreciated.

12. Any ideas would be appreciated!

13. Getting Viruses while Using Windows Update Appreciate any help and/or Ideas

14. Any ideas would be appreciated!

15. Plz HELP check codes before Viva next week!!!Your ideas are much appreciated...!

10 post • Page:**1** of **1**