Adding Cells based on number 3

[Denz]

Hi All,

Is there a way of working out how many cells should be added in a formula based upon the month number.

So for example if the month is 1 then the formula should be =A1

if month is 2 the formula should be =a1+a2

if month is 3 the formula should be =a1+a2+a3

etc etc

i just can't work out how to do this!!

Thanks for you help!

 

[SkipVought]

Denz,
[tt]
=SUM(A1:INDIRECT("A"&E1))
[/tt]
where E1 contains your MONTH value

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[GlennUK]

Have a look at the OFFSET function, which you could put inside a SUM function. You don't actually need to offset from your start cell, but you can specify the number of rows the returned range needs to cover.

Cheers, Glenn.

 

[SkipVought]

;-)

Ain't it great! All kinds of ways to skin a cat!

;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Zathras]

Hmmmm... Skip, I think the OP wanted to sum across row 1, not down column A.

Try this:

=SUM(INDIRECT("A1:"&CHAR(64+MONTH(NOW()))&"1"))

 

[SkipVought]

Z,
[tt]
if month is 3 the formula should be =a1+a2+a3
[/tt]
sure seems like COLUMN A to me???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Denz]

Yes, your right, it was my typo...

I need to add across rows, so should be A1+B1+C1

sorry about that.

Thanks Z for making me realise!!!

 

[SkipVought]

Geez Zathras! Now you've got a crystal ball!

I give up! Can't compete with such a medium at large!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Zathras]

That's what happens after the tenth cup of coffee!

Actually, I think I like Glenn's OFFSET solution best. The formula comes out like this:

=SUM(A1:OFFSET(A1,0,MONTH(NOW())-1))

My crystal ball shows that most users will put months across in a spreadsheet layout, not down. Since Denz calls himself a TechnicalUser and not a Programmer I naturally assumed he isn't as screwed up as we are!.

 

[SkipVought]

Well I am SURE of one thing.

Zathras is NOT a SMALL medium at large! He is a GIANT!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[GlennUK]

Hi Zathras super-psychic,

that OFFSET formula you've got can be simplied even further:

=SUM(OFFSET(A1,0,0,1,MONTH(NOW())))

which sums from A1 across however many columns for the months.

Cheers, Glenn.

 

[Zathras]

Thanks, Glen and Skip. As the good book says, "iron sharpens iron" - that's what makes this a great site.

 

[byundt]

Not to muddy the waters further, but OFFSET is a volatile function that is recalculated anytime anything on the worksheet changes. If your workbook is big and computation-intensive, then that will slow you down.

INDEX is not volatile, so consider:
=SUM(A1:INDEX(1:1,1,MONTH(TODAY())))

 

[Zathras]

byundt: Excellent contribution. Thank you.

Denz: I'm guessing here (crystal ball again), but you probably want to take the sum on each of several rows. In that case, no matter which approach you adopt, you should put
=MONTH(TODAY())
in a cell by itself and reference that cell in your SUM formulae. TODAY is also a "volatile" function and causes the current date and time to be fetched every time the worksheet is calculated.

You can test this yourself by filling a column with =TODAY() and see the effect on calculation speed.

 

[Denz]

Thanks all for your help!

I'll give those hints a try, hopefully the index function will work!

Zathras: This sheet is for a business year, so month 5 (may) is month one, and Month 1 (Jan) is month nine, i already have a vb script to sort that out, so i will just feed that variable into the index function... hopefully will give the results i want!

Thanks again all!!

 

[SkipVought]

Denz,

you got some pretty good advice and help from these guys. Please take note of the link in each of their replys that says [blue]Thank Zathras for this valuable post![/blue] AND [blue]GlennUK[/blue] AND [blue]byundt [/blue]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Denz]

Skip,

Your right...!

Thanks Guys!

 

[Denz]

HI Chaps,

i'm having a bit of trouble with this function!

what is needed now if for the row to be added in the following way:

in month 2
B1+B3

in month 3
B1+B3+B5

etc... so it doesn't add the column next to the last one, but skips one column each time...

Thanks for your help guys

 

[Zathras]

Since you are talking about skipping columns, I take your request to be interpreted this way:

what is needed now if for the row to be added in the following way:

in month 2
A1+C1

in month 3
A1+C1+E1


Building on byundt's formula I came up with this:
[tt]
=SUMPRODUCT(A1:INDEX(A1:X1,1,2*CURRENTMONTH),FIRSTONE:INDEX(ONESANDZEROS,1,2*CURRENTMONTH))
[/tt]
First notice that I changed the 1:1 reference to A1:X1 (24 columns instead of the entire row 1) This is not necessary if your data begin in column A, but in case they don't (my crystal ball says you have account descriptions or something like that in the first column or so) this allows the formula to be moved from A1 to wherever it is needed.

Then I created three ranges:
CURRENTMONTH is cell that contains the formula =MONTH(TODAY))
ONESANDZEROS is a range of 24 cells laid out horizontally that contains 1 in the first cell, 0 in the second, 1 in the third, and so on.
FIRSTONE is the cell that contains the first (left-most) cell of ONESANDZEROS.

You should put these data in some out-of-the-way area of your worksheet where they won't be seen (or on a different sheet altogether that you can then hide).

 

[Denz]

Cool Thanks Z... i'll try that when i get in from work..

your right again, it was columns, not rows (my excuse is that it was 6am when i posted this!)

I'll let you know how i get on...