sum several sheets and different columns

J

Jonsson

Hi all,

Any ideas of how to get this formula not so ugly:

=SUM(V5U!R5+V5U!S5)+(V5U!AA5+V5U!AB5)+(V5U!AJ5+V5U!AK5)+(V5U!AS5+V5U!AT5)+(V5U!BB5+V5U!BC5)+(V5U!BK5+V5U!BL5)+V6U!BM5+V7U!BM5+V8U!BM5+(V9U!I5+V9U!J5)

I have one week in every worksheet and trying to get a summary of
month in an easy way.

Thanks in advance

//Thoma
 
J

Jack Schitt

If you are just looking for neatness you could:
Define local named ranges
V5U!MyRange1 refers to
=V5U!$R$5:$S$5,V5U!$AA$5:$AB$5,V5U!$AJ$5:$AK$5,V5U!$AS$5:$AT$5,V5U!$BB$5:$BC$5,V5U!$BK$5:$BL$5
V9U!MyRange2 refers to =V9U!$I$5:$J$5

Then your formula might read

=SUM(V5U!MyRange1)+SUM(V6U:V8U!BM5)+SUM(V9U!MyRange2)

This assumes that V6U, V7U and V8U are adjacent sheets
Depending on the rest of the design you may wish to consider assigning
relative addresses to the defined names.
 
J

Jonsson

Hi Jack!
Thanks for answering!

I'm not looking for neatness, just a formula that will be so short and
simple as possible. I assume there must be a better way to write the
=SUMfunction than the way as I have done. But, maybe not....?

//Thomas
 
J

Jack Schitt

Presumably

=SUM(V5U!MyRange1)+SUM(V6U:V8U!BM5)+SUM(V9U!MyRange2)

can be further abbreviated to

=SUM(V5U!MyRange1,V6U:V8U!BM5,V9U!MyRange2)

--
Return email address is not as DEEP as it appears
Jack Schitt said:
If you are just looking for neatness you could:
Define local named ranges
V5U!MyRange1 refers to
=V5U!$R$5:$S$5,V5U!$AA$5:$AB$5,V5U!$AJ$5:$AK$5,V5U!$AS$5:$AT$5,V5U!$BB$5:$BC$5,V5U!$BK$5:$BL$5
V9U!MyRange2 refers to =V9U!$I$5:$J$5

Then your formula might read

=SUM(V5U!MyRange1)+SUM(V6U:V8U!BM5)+SUM(V9U!MyRange2)

This assumes that V6U, V7U and V8U are adjacent sheets
Depending on the rest of the design you may wish to consider assigning
relative addresses to the defined names.
Click to expand...
 
J

Jonsson

Hi,

I'd rather not use the "myRange"solution.

I have tried something like =SUM(V5U!R5:S5;AA5:AB5;AJ5:
AK5;AS5:AT5;BB5:BC5;BK5:BL5);V6U:V8U!BM5;V9U!I5:J5)

But that doesn't seem to be right.

Any help on this is much apprecciated!

//Thoma
 
J

Jack Schitt

Ok, how about

=SUM(V5U!R5:S5,V5U!AA5:AB5,V5U!AJ5:AK5,V5U!AS5:AT5,V5U!BB5:BC5,V5U!BK5:BL5,V6U:V8U!BM5,V9U!I5:K5)

Personally I think it would be easier to understand and maintain if you
broke it down a bit. If you don't want to use named ranges that that is
fine. How about reserving one cell in V5U to store totals relating to that
sheet, thus:

In cell V5U!CC5 store the formula
=SUM(R5:S5,AA5:AB5,AJ5:AK5,AS5:AT5,BB5:BC5,BK5:BL5)
Then your final formula would read
=SUM(V5U!CC5,V6U:V8U!BM5,V9U!I5:J5)

Indeed, if cell BM5 is free in sheets V5U and V9U then you could use V5U!BM5
to store the formula above suggested for cell CC5,
and in V9U enter the formula = I5+J5
and then your final formula would read
=SUM(V5U!V9U!BM5)

Personally I am a bit reluctant to use the sum-across-sheets syntax, ie
SUM(V6U!V8U!BM5) because it is not hard to move sheets around (unless the
workbook is protected) and if sheets get inserted or otherwise moved it
messes up the formula.
 
J

Jack Schitt

Typo
For V5U!V9U!BM5 read V5U:V9U!BM5
For V6U!V8U!BM5 read V6U:V8U!BM5.
But expect you understood that.

--
Return email address is not as DEEP as it appears
Jack Schitt said:
Ok, how about

=SUM(V5U!R5:S5,V5U!AA5:AB5,V5U!AJ5:AK5,V5U!AS5:AT5,V5U!BB5:BC5,V5U!BK5:BL5,V6U:V8U!BM5,V9U!I5:K5)

Personally I think it would be easier to understand and maintain if you
broke it down a bit. If you don't want to use named ranges that that is
fine. How about reserving one cell in V5U to store totals relating to
that sheet, thus:

In cell V5U!CC5 store the formula
=SUM(R5:S5,AA5:AB5,AJ5:AK5,AS5:AT5,BB5:BC5,BK5:BL5)
Then your final formula would read
=SUM(V5U!CC5,V6U:V8U!BM5,V9U!I5:J5)

Indeed, if cell BM5 is free in sheets V5U and V9U then you could use
V5U!BM5 to store the formula above suggested for cell CC5,
and in V9U enter the formula = I5+J5
and then your final formula would read
=SUM(V5U!V9U!BM5)

Personally I am a bit reluctant to use the sum-across-sheets syntax, ie
SUM(V6U!V8U!BM5) because it is not hard to move sheets around (unless the
workbook is protected) and if sheets get inserted or otherwise moved it
messes up the formula.
Click to expand...
 
J

Jonsson

Hi Jack!

I'm back and gonna try your suggestions!

Thanks for your effort to help me with this!!

//Thoma
 
Top