I'm trying to setup a formula that calculates the sum of a range if either of 2 cells are greater than zero but all it returns is a #VALUE! error. For example B4 has a value of 160, C4 is 0, D4 is 401000, E4 & F4 are both zero should return 401000 in G4. Whereas, B5 being 22032, C5 is 74000, D5 at 64000, E5 is 600 and F5 is 1110 should return 160,032. I have included what I've done so far as the formula with the error, but I'm stuck atm.
Please Login or Register to view this content.
this is untested, but try something like...
=if(and(E4>0,F4>0),sum(B4:D4),D4)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I am little confused in here, but i'll try :
here the table from your post:
B C D E F G 160 0 401000 0 0 401000 22032 74000 64000 600 1110 160032
with condition if E4 & F4 =0, then sum(C4 :D4) and if E4 or F4 > 0, then sum(B4:D4)
or if condition E4 & F4 =0 then G4=D4 and if E4 or F4 > 0 then sum(B4:D4), use this:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by SDCh; 02-09-2013 at 04:10 AM.
hi LoneWolf3574, try:
=IF(SUM(E4:F4)>0,SUM(B4:D4),D4)
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Ok, SDCh may have caught on to the mistake I made in the OP and FDibbins gave me the formula that I was able to modify to use. The issue comes up when either E4 or F4 is zero and the other is not, then the end result is wrong when it should be one of a combination of either D4, D4+B4, D4+C4 or D4+C4+B4. I'm using just the first row as an example here
The formula that I modified from FDibbins is -
It must be entered in this order or it will not work as desired, found that out the hard way :PPlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks