Variable sum, depending on first range

[fcastillo]

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.

[librebel]

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.

[fcastillo]

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!

[librebel]

You’re welcome @fcastillo, glad to read that it works