Confused..Need Help


Posted by Manu on January 23, 2002 6:01 AM

I have 4 columns(A B C D)with Data. How can I have Excel add up values in B C D based on a rule that I define for Data in A.

E.g. Col A has values like: 2, 5, 1, 4 etc... so, How can I accomplish if A < 5 then Excel adds up all values for respective B C D columns

All help is much appreciated.

Regards,

Posted by Juan Pablo G. on January 23, 2002 6:07 AM

In E2

=IF(A < 5,SUM(B2:D2),0)

Juan Pablo G.

Posted by Manu on January 23, 2002 6:51 AM

Tried your suggestion and am getting #NAME? in the destination cell. Any thoughts??

Regards,

Posted by Juan Pablo G. on January 23, 2002 6:59 AM

Typo. Try

=IF(A2 < 5,SUM(B2:D2),0)

or

=(SUM(B2:D2))*(A2 < 5)

Juan Pablo G.

Posted by Manu on January 23, 2002 7:18 AM

Thanks Juan,

What if I want to check for several rows in Col A, for instance I would like to add up values in B C D for all rows where A<1<p>Thanks for your help and patience.

Regards,

Posted by Corialanus on January 23, 2002 7:28 AM

=SUMIF(A2:A100,"<5",B2:B100)

Repeat for each column.

Posted by Juan Pablo G. on January 23, 2002 7:30 AM

If you want only the result of all calculations i think this will do it

=SUMIF($A$1:$A$20,"<1",B1:B20)+SUMIF($A$1:$A$20,"<1",C1:C20)+SUMIF($A$1:$A$20,"<1",D1:D20)

Where you want to add rows 1 through 20.

Juan Pablo G.

Posted by Manu on January 23, 2002 7:43 AM

MANY THANKS GENTLEMEN!!!!!

THIS SEEMS TO BE DOING THE TRICK....

INDEBTED INDEED,

REGARDS,



Posted by Manu on January 23, 2002 7:51 AM

One Additional piece of help , If I may please, What syntax would I use if need to define a range for values in Col A, for e.g. If A is >0 but <1 or if A >=16

All help is alwaya much appreciated.

Regards,