Flying Hours
 

I would like to have the sum of the last 3 entries in a column that have
spaces between the figures. I also need the sum of the last 12 entries in a
column that have spaces between the figures. Note: These figures are all in
column K.

Flying Hours
 

hi
can you post examples of your data that has "spaces between the figures"?

regards
FSt1

"Loadmaster" wrote:

I would like to have the sum of the last 3 entries in a column that have
spaces between the figures. I also need the sum of the last 12 entries in a
column that have spaces between the figures. Note: These figures are all in
column K.

Flying Hours
 

While I'm sure there must be a shorter solution, you can use the following
array-entered** formula to do what you asked...

=INDEX(K:K,LARGE(ROW(K1:K1000)*(K1:K1000<""),1))+ INDEX(K:K,LARGE(ROW(K1:K1000)*(K1:K1000<""),2))+I NDEX(K:K,LARGE(ROW(K1:K1000)*(K1:K1000<""),3))

** Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

I assumed a maximum data row of 1000, adjust as necessary for your actual
setup.

Rick


"Loadmaster" wrote in message
...
I would like to have the sum of the last 3 entries in a column that have
spaces between the figures. I also need the sum of the last 12 entries in
a
column that have spaces between the figures. Note: These figures are all
in
column K.

Flying Hours
 

Try this formula for last three numbers in K1:K100 (extend if necessary)

=SUM(IF(ROW(K1:K100)*ISNUMBER(K1:K100)=LARGE(ROW( K1:K100)*ISNUMBER(K1:K100),3),K1:K100))

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER so
that curly braces like { and } appear around the formula in the formula bar

For last 12 just change the 3 to a 12. If there are less than 3 (or 12)
numbers then all numbers will be summed

"FSt1" wrote:

hi
can you post examples of your data that has "spaces between the figures"?

regards
FSt1

"Loadmaster" wrote:

I would like to have the sum of the last 3 entries in a column that have
spaces between the figures. I also need the sum of the last 12 entries in a
column that have spaces between the figures. Note: These figures are all in
column K.

Flying Hours
 

If you only have numbers and/or spaces, try this *array* formula:

=SUM(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100< ""),3)))

If there might be text within the range, try this *array* formula:

=SUM(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100< "")*ISNUMBER(A1:A100),3))
)


Replace the 3 with a 12 to sum the last 12 rows.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Loadmaster" wrote in message
...
I would like to have the sum of the last 3 entries in a column that have
spaces between the figures. I also need the sum of the last 12 entries in
a
column that have spaces between the figures. Note: These figures are all
in
column K.

Flying Hours
 

On Sun, 13 Jul 2008 10:32:00 -0700, Loadmaster
wrote:

I would like to have the sum of the last 3 entries in a column that have
spaces between the figures. I also need the sum of the last 12 entries in a
column that have spaces between the figures. Note: These figures are all in
column K.


In general, this **array-entered** formula:

=SUM(LOOKUP(LARGE(ISNUMBER(rng)*ROW(rng),{1,2,3}), ROW(rng),rng))

To **array-enter** a formula, hold down <ctrl<shift while hitting <enter. If
you did it correctly, Excel will place braces {...} around the formula.

For versions of Excel prior to 2007, rng cannot refer to the entire column. So
the largest rng would be K1:K65535.

In Excel 2007, rng could refer to the entire column K:K

For the top 12:

=SUM(LOOKUP(LARGE(ISNUMBER(rng)*ROW(rng),ROW(INDIR ECT("1:12"))),ROW(rng),rng))

also **array-entered** with <ctrl<shift<enter.
--ron