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,
