Sum cells based on number of months

[rck]

What would the formula be to add 7 months worth of data based on an index.
i.e I have 12 cells with data but only want to add say 7 of them for YTD data
through july. If I use and index number of say 7 in cell a1 what would the
formula be to add only the first 7 cells listed out of 12 cells -
c5,e5,g5,i5,k5,m5,o5,q5,s5,u5,w5,y5 Hope this makes sense.

 

[Pete_UK]

This array* formula will do the trick:

=SUM(IF((MOD(COLUMN(C5:Y5),2)=1)*(INT((COLUMN(C5:Y5)-1)/2)
<=A1),C5:Y5))

* An array formula has to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this
correclty then Excel will wrap the formula in curly braces { } when
viewed in the formula bar - do not type these yourself. If you edit
the formula you will need to use CSE again.

Hope this helps.

Pete

 

[T. Valko]

If this is a YTD total then does that maen there *isn't* any data for Aug
through Dec? Are those cells empty or might they contain a numeric 0?

Your range is every other cell. What's in the cells between? What's in D5,
F5, H5, J5 etc.?

 

[rck]

Here is the actual formula to add 12 months of budgeted data - all cells
contain a number:

=SUM(D40+I40+N40+S40+X40+AC40+AH40+AM40+AR40+AW40+BB40+BG40)

By putting a value in cell a1 - say 7 for July - How would I modify this to
say add the first 7 cells for July YTD - but leave all 12 cell refereces so
that in August I can change cell A1 to 8 and have it sum 8 months of data?
Thanks in advance.

Bob K.

 

[Pete_UK]

Try this variation of the array* formula I gave you yesterday:

=SUM(IF((MOD(COLUMN(D40:BG40)+1,5)=0)*(INT((COLUMN(D40:BG40)+1)/5)
<=A1),D40:BG40))

* An array formula has to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this
correclty then Excel will wrap the formula in curly braces { } when
viewed in the formula bar - do not type these yourself. If you edit
the formula you will need to use CSE again.

Hope this helps.

Pete

 

[rck]

Hi Pete,
Thanks a bunch!! Works perfectly!!! Now if I can only understand the
formula.......

Best Regards,
Bob K.

 

[Pete_UK]

You're welcome, Bob - thanks for feeding back.

Your data is in every 5th column, starting with the 4th (column D), so
this part of the formula:

(MOD(COLUMN(D40:BG40)+1,5)=0)

sets up a condition to take data only from those columns by adding 1
to the column number (to make it 5, 10, 15 etc) and then dividing by 5
and taking the value only when the remainder is zero.

The next part of the formula:

(INT((COLUMN(D40:BG40)+1)/5)<=A1)

ensures that data is taken only from those column blocks which are
less than or equal to the index in cell A1.

Both these conditions have to be met for the data to be summed to give
you the result.

Hope this helps.

Pete