Ask Your Question
0

Variable sum, depending on first range

asked 2017-07-23 21:36:26 +0200

fcastillo gravatar image

The title might be confusing, but here's what I want to do:

Let's say I have 4 columns, each has 100 elements. Each column has a sum of a range of its values, so let's say the first 50 values. I want to be able to change the range of the first column and all the other summations will also get updated with the same first range change. So if the first changes to sum up to the first 70 values, all other summations also show the first 70 of their respective columns. The range can increase or decrease.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-07-24 10:14:23 +0200

librebel gravatar image

Hello @fcastillo,

you could start out by reserving 2 special cells, let's say K1 and K2, where you can manually enter the Start Row Number and the End Row Number of the range to be subtotalled. For a range of the first 70 values, you would enter 1 in K1 and 70 in K2.

then for the subtotals of column 1, you could enter a formula such as:

=SUM(INDIRECT(CONCAT(ADDRESS(K1;1);":";ADDRESS(K2;1))))

for the subtotals of column 2:

=SUM(INDIRECT(CONCAT(ADDRESS(K1;2);":";ADDRESS(K2;2))))

etc. for further columns.

If you want the subtotals to be updated immediately when the user selects a new range in your first Column, then you'd need to write a macro and connect it to the Sheet's "Selection Changed" event ( menu "Sheet : Sheet Events..." ).

There you could either programmatically update your special cells ( K1 and K2 in this example ), or you could do the whole thing without any special cells at all.

edit flag offensive delete link more

Comments

Thanks so much!!! I never thought of doing that and it works great! The macro also seems like a good solution but I rather not use them. Thanks again, this worked great!

fcastillo gravatar imagefcastillo ( 2017-07-25 22:09:31 +0200 )edit

You're welcome @fcastillo, glad to read that it works :)

librebel gravatar imagelibrebel ( 2017-07-25 22:16:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-07-23 21:36:26 +0200

Seen: 41 times

Last updated: Jul 24 '17