Excel - Sum last populated columns
I think this is pretty easy but I don't have a concise way to do it.
This is a table with month-dates as the column heading.
Every month, new data is added to a column (not inserted, but updated through Essbase).
The formula I'm looking for will sum the last three columns -- each month shifting over one.
I think something like countif>0 would give the number of columns, then subtract three for the first column to use and go to the max value as the last - would work.
But how does that translate into the actual cells to use? Is it an Indirect or Address command with the column and row taken from the formula?
Thanks for your help.
Excel 2003
Sum-Last-columns.xls
This is a table with month-dates as the column heading.
Every month, new data is added to a column (not inserted, but updated through Essbase).
The formula I'm looking for will sum the last three columns -- each month shifting over one.
I think something like countif>0 would give the number of columns, then subtract three for the first column to use and go to the max value as the last - would work.
But how does that translate into the actual cells to use? Is it an Indirect or Address command with the column and row taken from the formula?
Thanks for your help.
Excel 2003
Sum-Last-columns.xls
Please define what you mean by "last populated columns". For example, in your sample sheet, Cols I:M are populated; they just happen to be populated with zeroes. (And on closer inspection, they are actually text, and not numeric, values at that.)
So, based on your sample, please indicate which columns would qualify.
So, based on your sample, please indicate which columns would qualify.
ASKER
Sorry - that should have been non-zero values. The file puts those zeros in, then each month numeric values replace them.
ASKER
That was not a good description -- sorry!
Broomee -- I should have said, sum of the last three columns for each row.
So, last three, non-zero columns in row 3 - result in N3.
Last three, non-zero columns in row 4 - results in N4.
The task is probably simpler than you thought. :-)
I could do it just with =SUM(F3:H3) -- then drag that down column N.
Next Month, it's =SUM(G3:I3) --- again in column N.
So, the formula needs to figure out which are the last three populated non-zero (they're always the last three before the zeros) and then sum them.
Or, perhaps some kind of OFFSET function to move them each month?
Broomee -- I should have said, sum of the last three columns for each row.
So, last three, non-zero columns in row 3 - result in N3.
Last three, non-zero columns in row 4 - results in N4.
The task is probably simpler than you thought. :-)
I could do it just with =SUM(F3:H3) -- then drag that down column N.
Next Month, it's =SUM(G3:I3) --- again in column N.
So, the formula needs to figure out which are the last three populated non-zero (they're always the last three before the zeros) and then sum them.
Or, perhaps some kind of OFFSET function to move them each month?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's great -- works perfectly. The Max is getting the largest count on the columns. Using Count is a great idea also -- I was thinking I had to do a Countif,<>0 or something like an IF<>0 and that was too complicated.
Many thanks.
Many thanks.
=SUM(INDIRECT(ADDRESS(3,CO
See attached. Note that this sums row 3 to row 20.
Sum-Last-columns-1-.xls