Flying Hours

[Loadmaster]

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.

 

[FSt1]

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

regards
FSt1

 

[Rick Rothstein \(MVP - VB\)]

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))+INDEX(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

 

[daddylonglegs]

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

 

[Ragdyer]

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.

 

[Ron Rosenfeld]

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(INDIRECT("1:12"))),ROW(rng),rng))

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