 |
 | 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 / June 2007index and sum nonadjacent cells
Hello! I need to create a formula that will sum a series of nonadjacent cells, but will be able to limit how many of the cells to sum based on the index. For example, I have a dollar total based on 12 months of data: cell: J10 O10 T10... Mo.: Jan Feb Mar... Amt:$100 $100 $100... and so on However, months that are in the future have "guesstimate" data in them. I would like to have a formula that will run on a Year-To-Date basis that will only sum the ACTUAL data, without having to add a cell each month to include that new total. (So if executed today, the formula would only sum data for January through June, but next month, would sum January through July without having to change anything but the index.) Any ideas? Thank you! One way: =SUM(OFFSET(J10,0,0,1,MONTH(TODAY()))) > Hello! > I need to create a formula that will sum a series of nonadjacent cells, but [quoted text clipped - 12 lines] > > Any ideas? Thank you! Oops, didn't see that the columns were offset. Try: =SUMPRODUCT(--(MOD(COLUMN(J10:BM10),5)=0), --(COLUMN(J10:BM10)<=MONTH(TODAY())*5+5), J10:BM10) In article <jemcgimpsey-4148AF.09100612062007@msnews.microsoft.com>, > One way: > [quoted text clipped - 19 lines] > > > > Any ideas? Thank you! hmm...When I copy into the cell, it returns a "DIV/0!" > Oops, didn't see that the columns were offset. Try: > [quoted text clipped - 26 lines] > > > > > > Any ideas? Thank you! There's nothing in the formula that would return that. Is there a value in J10:BM10 that has that error in it? > hmm...When I copy into the cell, it returns a "DIV/0!" it seems like what is happening is the formula is trying to include all the cells between J10 and BM10, but I only want to sum 12 specific cells in that row (they're nonadjacent)--but it's very possible i just don't know the formula language entirely. but to answer your question, yes, there are several cells that have "div/0" in them, but they are among the cells that i don't want involved in the formula. here are all the cells that I want to eventually be included in the formula: J10 (Jan), O10 (Feb), T10 (Mar), Y10 (Apr), AD10 (May), AI10 (Jun), AN10 (Jul), AS10 (Aug), AX10 (Sep), BC10 (Oct), BH10 (Nov), BM10 (Dec). But the formula we're trying to come up with should only sum the cells within that series that have already occured (Jan through May, for now). The rest of the cells in row 10 in between all these cells have formulas in them that as of now are returning DIV/0 because those months haven't happened yet and there is no data as of now (which is ok as far as I'm concerned; it just messes up the year-to-date info). This is where I'm having trouble. Thanks for all your help! > There's nothing in the formula that would return that. Is there a value > in J10:BM10 that has that error in it? > > > hmm...When I copy into the cell, it returns a "DIV/0!" Unless you want to hardcode each cell, you should trap the errors instead of leaving them as "expected errors". This is good practice anyway. For example, instead of P10: =N10/O10 where O10 may be zero, use P10: =if(O10<>0,N10/O10,"") SUM() (and SUMPRODUCT()) will then ignore the null string. > This is where I'm having trouble. Thanks for all your help! great! that worked as far as getting the SUMPRODUCT formula to bring back a value. however, i'm still having trouble getting it to only sum specific cells in that row. it's not returning an error, but it is adding everything in that row. is there a way to select specific cells to sum together? Thanks--this is a big help as it is and will be useful in many other applications. > Unless you want to hardcode each cell, you should trap the errors > instead of leaving them as "expected errors". This is good practice [quoted text clipped - 11 lines] > > > This is where I'm having trouble. Thanks for all your help! Yes, the second formula I gave you only sums every 5th column. > great! that worked as far as getting the SUMPRODUCT formula to bring back a > value. however, i'm still having trouble getting it to only sum specific > cells in that row. it's not returning an error, but it is adding everything > in that row. is there a way to select specific cells to sum together? =SUMPRODUCT(--(MOD(COLUMN(J10:IV10),5)=0),J10:IV10) > Hello! > I need to create a formula that will sum a series of nonadjacent cells, but [quoted text clipped - 12 lines] > > Any ideas? Thank you! Also returns "DIV/0!" error...I must be missing something! > =SUMPRODUCT(--(MOD(COLUMN(J10:IV10),5)=0),J10:IV10) > [quoted text clipped - 14 lines] > > > > Any ideas? Thank you! |
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.