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?

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?

Sorry, forgot to mention, that i built my formula using data in range
A1:F1. Where F1 would be your last known cell.

If there are any problems with adjusting the formula, just tell
me what your last cell will be.

Cheers

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?

=SUM(N(OFFSET(A1,0,LARGE(IF(A1:F1<"",COLUMN(A1:F1 )-1),{1,2,3,4}))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Carl" wrote in message
...
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?

Try this:

=SUM(F1:INDEX(A1:F1,LARGE(COLUMN(A1:F1)*(A1:F1<"" ),4)))

ctrl+shift+enter, not just enter


"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?

Another way...
None array formula, it doesn't required ctrl+shift+enter, Just ENTER

=SUM(F1:INDEX(A1:F1,LARGE(INDEX(COLUMN(A1:F1)*(A1: F1<""),0),4)))


"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?

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?

OK, I tried all of the suggestions offered and did not get satisfactory
results with any of them. I'm not sure what happened with my earlier replay
thanks ya'll for the inputs. I had to use Excel help to find out what a few
of the functions did so I learned from that but I still dn't know what the
N() function does.

I did adjust everyone of the formulas offered to match my actual data and
maybe that had some impact on the results.

I created a separate spreadsheet with all of the formulas and their
respective results as well as a breakdown of the formulas to help me
understand how they worked. I would gladly provide this spreadsheet to
anyone willing to provide additional help but I don't know if there is a way
to attach it to this thread.

Again thanks for the positive feedback.

"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?

N Just converts a cell to its numeric equivalent, required as OFFSET returns
an array of cell references, N changes it to an array of values (to SUM).

Send it to me at

bob dot phillips at tiscali dot co dot uk

do the obvious with dot at and the spaces, and I will look at it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Carl" wrote in message
...
OK, I tried all of the suggestions offered and did not get satisfactory
results with any of them. I'm not sure what happened with my earlier
replay
thanks ya'll for the inputs. I had to use Excel help to find out what a
few
of the functions did so I learned from that but I still dn't know what the
N() function does.

I did adjust everyone of the formulas offered to match my actual data and
maybe that had some impact on the results.

I created a separate spreadsheet with all of the formulas and their
respective results as well as a breakdown of the formulas to help me
understand how they worked. I would gladly provide this spreadsheet to
anyone willing to provide additional help but I don't know if there is a
way
to attach it to this thread.

Again thanks for the positive feedback.

"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?