 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Subtract two columns, but only for cells identified by if statemen
I have 3 columns of information, A, B, C. C is defined by an if statement if(B<A,"yes", ""). Now I would like to find the difference between A & B, for all the cells in the column that received a "yes" and add to one total. Is this possible? I'm assuming you want this difference plus one to be in another column... =IF(C1="yes",B1-A1+1,"") or, if you want the formula to only reference A and B, then use the condition that produced the "yes" value... =IF(B1<A1,B1-A1+1,"") Rick >I have 3 columns of information, A, B, C. C is defined by an if statement > if(B<A,"yes", ""). [quoted text clipped - 3 lines] > > Is this possible? Thank you, but I'm looking for something a little different. I would like it to add up the differences into one total at the bottom (I don't want individual differences for each row). Does that make since? Say I have 4 rows of info....and only two of them are "yes" in column C, I would like for it to add the two differences into 1 total at the bottom of column C. > I'm assuming you want this difference plus one to be in another column... > [quoted text clipped - 14 lines] > > > > Is this possible? Okay, I think Bob has given you the formula you need. I just re-read your post again and see I misinterpreted your... Now I would like to find the difference between A & B, for all the cells in the column that received a "yes" and add to one total as asking for one to be added to the difference (which is what led me to conclude you wanted a row-by-row solution). Rick > Thank you, but I'm looking for something a little different. > I would like it to add up the differences into one total at the bottom (I [quoted text clipped - 24 lines] >> > >> > Is this possible? Hmm! I see Bob read your request differently than I did. I assumed you want the calculations on a row-by-row basis whereas he assumed you wanted them in toto. In re-reading your post, I am still not sure which you are after. Rick > I'm assuming you want this difference plus one to be in another column... > [quoted text clipped - 14 lines] >> >> Is this possible? I am looking for one total, but I only want it to add up those cells that fit my if criteria, i.e. 7.25>A. It is not two separate columns either....it is one fixed number. > Hmm! I see Bob read your request differently than I did. I assumed you want > the calculations on a row-by-row basis whereas he assumed you wanted them in [quoted text clipped - 20 lines] > >> > >> Is this possible? =SUMPRODUCT(--(A2:A200>B2:B200), A2:A200-B2:B200)  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I have 3 columns of information, A, B, C. C is defined by an if statement > if(B<A,"yes", ""). [quoted text clipped - 3 lines] > > Is this possible? I'm sorry...I've been staring at this for so long, I confused myself.... This is almost what I want...and I tried to edit it to get what I wanted...but that didn't work. It is actually not two different columns of info, rather it is one number, say 7.25. If 7.25>A2:A63, if it is a larger number, then I want to add up the difference between 7.25 and A into one total at the bottom. Like I said I tried to edit your formula below, but the anser it gave me, is not the same as when I calculate it manually. I'm sure I am missing something real easy. > =SUMPRODUCT(--(A2:A200>B2:B200), A2:A200-B2:B200) > [quoted text clipped - 5 lines] > > > > Is this possible? =SUMPRODUCT(--(A2:A20>7.25),A2:A20-7.25) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > I'm sorry...I've been staring at this for so long, I confused myself.... > [quoted text clipped - 22 lines] >> > >> > Is this possible? Thank you so much for your patience.... I updated my info using the below, however the total I am getting is not the same as manually calculating it.... Am I suppose to the replace the -- with something? > =SUMPRODUCT(--(A2:A20>7.25),A2:A20-7.25) > [quoted text clipped - 24 lines] > >> > > >> > Is this possible? Absolutely not! Can you post the data so that I can check it through? Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Thank you so much for your patience.... > I updated my info using the below, however the total I am getting is not [quoted text clipped - 35 lines] >> >> > >> >> > Is this possible? I tried to sign back on yesterday to let you know that it did work. I reviewed my columns and didn't have all of the cells in the formula. Thanks a million for your help! Question though....what is the --? Is that just like writing "if"? > Absolutely not! > [quoted text clipped - 39 lines] > >> >> > > >> >> > Is this possible? No, it is forcing some value into its numeric equivalent. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed discussion. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I tried to sign back on yesterday to let you know that it did work. I > reviewed my columns and didn't have all of the cells in the formula. [quoted text clipped - 49 lines] >> >> >> > >> >> >> > Is this possible? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.