Dynamic SUM range
Wow! What a great response from Carlo, Bob and Teethless Mama. I will try
each of the recommendations ya'll offered and let you know what I learned and
which one I ended up with.
Just a though, I said I knew the exact location of the last cell I would use
as my starting point, but I didn't mention that that starting location was
subject to change. I know I can use END+right arrow to position the active
cell to the last cell in the row, but how do I then translate that cell into
the formula?
Also, I use Excel 2002, does that have any impact on the formulas offered?
Thanks again for all your thoughts and ideas.
"Carl" wrote:
I want to be able to SUM the last 4 values in row. I know the exact location
of the last cell so this would appear to be my starting point and I would
work backwards from thee. But the cells preceding it may contain a value or
there may be a blank cell. There can only be a maximum of 1 blank cell in
the row of values I want to sum, so the blank cell may or may not be in the
last 4 cells I want to sum. If it is one of the last 4 cells then I want to
go back 5 cells so that I end up with 4 values summed.
Example: 14 20 23 38 41 blank 28 16
Since I want to sum the last 4 cells with values, I know I want to sum
38+41+28+16 but I have to define my range for the last 5 cells to get 4 valid
cells
But if I have...
Example: 14 20 23 38 41 28 16
I still want to sum the last 4 values but now the range is only the last 4
cells because there is no blank cell in the range.
Am I trying to make this too complex?
|