Forum Discussion
Multi-line range used for summing specific column
I have a file in which I'm going to be constantly expanding the vertical length of a 36 month span of months. Specifically, I'll have various sections (all very similar, but not identical, in structure). They will have certain cost lines (which will vary), and then they'll total to a "Subtotal Cost" line, per section. I need a 'Total Cost' of all the 'Subtotal Cost' lines. (To be clear, the way the file is structured, I cannot use the subtotal function to avoid double counting)
What I'd like to do is name a range as being all subtotal lines (so exactly the same 36 columns of width, but rows in various places in the file. My thought was then to make something like an index of offset formula within a Sum function, but I can't figure out how to effectively say "all the X columns of each row. Here's what I have:
=SUM(INDEX(_testrange2,1,C$15))
where "_testrange2" is all the rows, of same width, in a single defined range. C$15 happens to be the row in which I'm placing the number of the month, with the logic being as I copy this formula across, it would grab the appropriate month column.
Whether I enter, ctrl-enter, or ctrl-shft-enter the above, get the same result.
How do I get it to understand I want all rows, X column?
The reason I need this is that as I add sections, I'd simply adjust the named range with the additional subtotal line of the new section, thus updating all instances where I need to get the total.
Thanks for any/all suggestions.
10 Replies
- I cannot imagine the formula to return the result you want unless you attach a sample file.
A11 should read 14 (because right now the named range only includes the 5th and 7th row. Once I update the range to also include the 9th row, I'll want the row 11 result to automatically show 27 (the addition of 13 from A9)
hope that clarifies.
In the attached file, the formula in A11 is:
=SUM(INDEX(A:A,G1):INDEX(A:A,G2))
G1 and G2 are the cell references for the start and end of the row to sum, respectively.
- attaching in 2minutes
- I would not try to combine data and a formated report in one sheet. Why not make sure you have columns next to the data for whatever categories or other groupings you need for subtotalling and then use a pivot table to create the report and its sub-totals.
Thought I'd give a sense of what I'm dealing with, as I'd love thoughts on best way to implement pivots for this. The attached is a fraction of an extract, which shows the basic dynamics:
- there is a section of assumptions (this is one line of assumption...there are about 20-30 per section, and currently there are 20 sections)
- there is an actual time of months (this shows 12, but it's a 3 year model, so there are 36 columns of months
- this is a set of assumptions that occur on an absolute time basis (the assumptions say between months 1-5 there are 50 members acquired, and then from month 6 onward there will be 100 per month)
- there are certain assumptions (not shown here, to keep it less complicated), which are 'as of joining' dynamics...so it'll say from month 1-3 there will be no fee charged, and then from months 4-6 a 50% fee will be charged, and then followed by full fee). This means that for a customer joining in month 3, their first month (the "month 1" as of joining) is in actuality month 3 of real time...so the 'as of joining' assumption would be applied starting in actual month 3, for 3 months (up to month 6), then the as of joining assumption between month 4-6 applied to actual month 7-9...
and the above dynamics would be recreated for every month's group of customers, for 36 months...and there will be between 20-40 versions of all that.
Hence I put it horizontally for time, sum it up by section vertically, and need to be able to expand/contract the spots vertically that I'm capturing.
Currently the file is 14K lines long, and that's just for the revenue assumptions of the various scenarios we're envisioning (this is a broad platform we're creating).
That said, I'd LOVE to do this with pivots. The only way I could think of doing it would be to flip the entirety of my calculations into a vertical stream, where each line represents one month's worth of one customer's worth of data.
If you have ideas of how to efficiently use pivots to calculate the elements once, and then be able to flip them around for various reporting needs, would welcome your suggestions.
Thanks.
It's an option. It's just not the way this file works, for various reasons that would take too long to describe. I've just got to believe there's a way to effective take an array and (formulaically, not VBA) use the rows as I would any set of rows in Excel, whereby I can take the Xth column, and sum all amounts in that column.
Thanks for the idea of separating into pivots. Again, just not in the cards for this file, for a host of reasons.
