Dynamic SUM range
Hi Carl
you could do it like that:
Where the last cell would be F1
=SUM(INDIRECT(CHAR(65 + 6 - 4 - COUNTBLANK(B1:F1)) & "1:F1"))
To change the last column use (64 + column of last cell - 4).
Hth
Carlo
"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?
|