Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index 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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
One way:
=SUM(OFFSET(J10,0,0,1,MONTH(TODAY()))) In article , Sam wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
=SUMPRODUCT(--(MOD(COLUMN(J10:IV10),5)=0),J10:IV10)
"Sam" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
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 , JE McGimpsey wrote: One way: =SUM(OFFSET(J10,0,0,1,MONTH(TODAY()))) In article , Sam wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
hmm...When I copy into the cell, it returns a "DIV/0!"
"JE McGimpsey" wrote: 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 , JE McGimpsey wrote: One way: =SUM(OFFSET(J10,0,0,1,MONTH(TODAY()))) In article , Sam wrote: 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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
Also returns "DIV/0!" error...I must be missing something!
"Teethless mama" wrote: =SUMPRODUCT(--(MOD(COLUMN(J10:IV10),5)=0),J10:IV10) "Sam" wrote: 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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
There's nothing in the formula that would return that. Is there a value
in J10:BM10 that has that error in it? In article , Sam wrote: hmm...When I copy into the cell, it returns a "DIV/0!" |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
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! "JE McGimpsey" wrote: There's nothing in the formula that would return that. Is there a value in J10:BM10 that has that error in it? In article , Sam wrote: hmm...When I copy into the cell, it returns a "DIV/0!" |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
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. In article , Sam wrote: This is where I'm having trouble. Thanks for all your help! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
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. "JE McGimpsey" wrote: 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. In article , Sam wrote: This is where I'm having trouble. Thanks for all your help! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and sum nonadjacent cells
Yes, the second formula I gave you only sums every 5th column.
In article , Sam wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i add numbers in nonadjacent cells? | Excel Worksheet Functions | |||
How do I search for a total using nonadjacent cells | Excel Worksheet Functions | |||
How do I fill (copy) nonadjacent cells to adjacent cells? | Excel Discussion (Misc queries) | |||
functions with nonadjacent cells | Excel Worksheet Functions | |||
Use TREND function with nonadjacent cells | Excel Worksheet Functions |