I have been trying to get my head around something all morning and I
just can't seem to get it to work I am usually good with embedded if
statements and the like but I just can't come up with a sum that will do
this.

In O1 I have a control which can range from 0 to 5, 0 being no weeks of
the month have passed and and 1-5 being the number of the weeks that
have passed in for the current month. Our accounting means we have five
week months occasionally.

In week 0 this is the sum -
Sum(I18+J18+U18+V18+W18+X18+Y18+AC18+AD18+AE18+AF1 8+AG18)

For week 1 the sum is SUM(I18+J18+V18+W18+X18+Y18,AC18)

For week 2 the sum is SUM(I18+J18+W18+X18+Y18+AC18+AD18)

For week 3 the sum is SUM(I18+J18+X18+Y18+AC18+AD18+AE18)

For week 4 the sum is SUM(I18+J18+Y18+AC18+AD18+AE18+AF18)

And for week 5 the sum is SUM(I18+J18+AC18+AD18+AE18+AF18+AG18)

Can I put all of that into one sum dependent on the value in 01 being
0,1,2,3,4 or 5?

I have tried If but I can't seemt get it to work and I am usually
pretty good with them.

Does anyone have any other ideas?

Thanks in advance.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52790

Replace A2 with whichever cell you keep the week number in.

=IF(A2=0,SUM(I18:J18,U18:Y18,AC18:AG18),
IF(A2=1,SUM(I18:J18,V18:Y18,AC18),
IF(A2=2,SUM(I18:J18,W18:Y18,AC18:AD18),
IF(A2=3,SUM(I18:J18,X18:Y18,AC18:AE18),
IF(A2=4,SUM(I18:J18,Y18,AC18:AF18),
IF(A2=5,SUM(I18:J18,AC18:AG18),"Not Valid"))))))

"Timbo" wrote:


I have been trying to get my head around something all morning and I
just can't seem to get it to work I am usually good with embedded if
statements and the like but I just can't come up with a sum that will do
this.

In O1 I have a control which can range from 0 to 5, 0 being no weeks of
the month have passed and and 1-5 being the number of the weeks that
have passed in for the current month. Our accounting means we have five
week months occasionally.

In week 0 this is the sum -
Sum(I18+J18+U18+V18+W18+X18+Y18+AC18+AD18+AE18+AF1 8+AG18)

For week 1 the sum is SUM(I18+J18+V18+W18+X18+Y18,AC18)

For week 2 the sum is SUM(I18+J18+W18+X18+Y18+AC18+AD18)

For week 3 the sum is SUM(I18+J18+X18+Y18+AC18+AD18+AE18)

For week 4 the sum is SUM(I18+J18+Y18+AC18+AD18+AE18+AF18)

And for week 5 the sum is SUM(I18+J18+AC18+AD18+AE18+AF18+AG18)

Can I put all of that into one sum dependent on the value in 01 being
0,1,2,3,4 or 5?

I have tried If but I can't seemt get it to work and I am usually
pretty good with them.

Does anyone have any other ideas?

Thanks in advance.


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=52790

On Jan 21, 3:38 am, Timbo wrote:
Can I put all of that into one sum dependent on the value
in 01 being 0,1,2,3,4 or 5?

At a minimum, take a look at the CHOOSE function. (Also, I see no
point in your using the SUM function per se.) In your case, you would
write:

=choose(O1+1,
I18+J18+U18+V18+W18+X18+Y18+AC18+AD18+AE18+AF18+AG 18,
I18+J18+V18+W18+X18+Y18+AC18, ...etc...)

But I see some opportunity for simplication. At a mimimum, all of the
sums include I18+J18+AC18. So you could take out the common term.
For example:

=I18+J18+AC18
+ choose(O1+1, U18+V18+W18+X18+Y18+AD18+AE18+AF18+AG18,
V18+W18+X18+Y18, ...etc...)

Other manipulations come to mind, but they are only half-baked at the
moment. Not sure they are worth the effort. For example, a clever
use of SUM and OFFSET might simplify the two subexpressions involving
the sums of variable subsets of U18:Y18 and AC18:AG18.




On Jan 21, 3:38*am, Timbo wrote:
I have been trying to get my head around something all morning and I
just can't seem to get it to work I am usually good with embedded if
statements and the like but I just can't come up with a sum that will do
this.

In O1 I have a control which can range from 0 to 5, 0 being no weeks of
the month have passed and and 1-5 being the number of the weeks that
have passed in for the current month. Our accounting means we have five
week months occasionally.

In week 0 this is the sum -
Sum(I18+J18+U18+V18+W18+X18+Y18+AC18+AD18+AE18+AF1 8+AG18)

For week 1 the sum is SUM(I18+J18+V18+W18+X18+Y18,AC18)

For week 2 the sum is SUM(I18+J18+W18+X18+Y18+AC18+AD18)

For week 3 the sum is SUM(I18+J18+X18+Y18+AC18+AD18+AE18)

For week 4 the sum is SUM(I18+J18+Y18+AC18+AD18+AE18+AF18)

And for week 5 the sum is SUM(I18+J18+AC18+AD18+AE18+AF18+AG18)

Can I put all of that into one sum dependent on the value in 01 being
0,1,2,3,4 or 5?

I have tried If but I can't seemt get it to work and I am usually
pretty good with them.

Does anyone have any other ideas?

Thanks in advance.

--
Timbo
------------------------------------------------------------------------
Timbo's Profile:http://www.thecodecage.com/forumz/member.php?userid=24
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=52790