Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam Sam is offline
external usenet poster
 
Posts: 699
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam Sam is offline
external usenet poster
 
Posts: 699
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam Sam is offline
external usenet poster
 
Posts: 699
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam Sam is offline
external usenet poster
 
Posts: 699
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam Sam is offline
external usenet poster
 
Posts: 699
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i add numbers in nonadjacent cells? Bob Excel Worksheet Functions 1 April 2nd 06 05:04 AM
How do I search for a total using nonadjacent cells rwfrench Excel Worksheet Functions 3 March 20th 06 03:56 AM
How do I fill (copy) nonadjacent cells to adjacent cells? BuckyGeorge Excel Discussion (Misc queries) 2 December 22nd 05 05:18 AM
functions with nonadjacent cells PCdummy Excel Worksheet Functions 1 August 29th 05 03:59 PM
Use TREND function with nonadjacent cells BonesCCS Excel Worksheet Functions 0 August 4th 05 10:59 PM


All times are GMT +1. The time now is 04:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"