index and sum nonadjacent cells

[Sam]

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!

 

[JE McGimpsey]

One way:

=SUM(OFFSET(J10,0,0,1,MONTH(TODAY())))

 

[Teethless mama]

=SUMPRODUCT(--(MOD(COLUMN(J10:IV10),5)=0),J10:IV10)

 

[JE McGimpsey]

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)

 

[Sam]

hmm...When I copy into the cell, it returns a "DIV/0!"

 

[Sam]

Also returns "DIV/0!" error...I must be missing something!

 

[JE McGimpsey]

There's nothing in the formula that would return that. Is there a value
in J10:BM10 that has that error in it?

 

[Sam]

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]

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.

 

[Sam]

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]

Yes, the second formula I gave you only sums every 5th column.