Sum cells based on number of months

R

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.
 
P

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

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.?
 
R

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.
 
P

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
 
R

rck

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

Best Regards,
Bob K.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top