sum of last 3 figures in a column which also contain empty cells

[Loadmaster]

I am looking to find the sum of all the last 3 figures in Column K (Note:
Column K contains empty cells between the figures). Also, I am looking for
the sum of all the last 12 figures in that same column (Column K).

 

[Rick Rothstein \(MVP - VB\)]

Was anything wrong with either of the two answers you got 2.5 hours ago to
this identical question that you posted in the worksheets.function
newsgroup???

Rick

 

[Ron Rosenfeld]

I am looking to find the sum of all the last 3 figures in Column K (Note:
Column K contains empty cells between the figures). Also, I am looking for
the sum of all the last 12 figures in that same column (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

 

[Loadmaster]

My apologize, for the double entry and my tardiness in responding. The emails
that I received had blank pages for the replies. I searched the website
looking for my initial query and found I was looking in the excel worksheet
functions instead. Anyhow, the reason why the formula didn’t work is because
I explained it wrong.

1 A B C D etc to K
2 Sqn Year Month Day Monthly
Totals

161 2008 Apr 23 36.7

162 2008 May 29 6.1

167 2008 Jun 28 23.4

As you can see from above the most recent entries are at the bottom of the
column, Row 167 onward. There are various empty cells between the figures in
column K depending on how many hours I fly in any one particular month. All
other cell Columns have entries in them even it the squadron, month, or year
is repeated within the columns.

 

[Loadmaster]

This must be a tough formula to figure out with the new perameters i just
specified this morning.

 

[Ron Rosenfeld]

This must be a tough formula to figure out with the new perameters i just
specified this morning.

Not really. What "new" parameters? I thought you just wanted the last
(bottom) 3 or 12 entries?

What is hard is when you don't give us any information as to the problems with
the solutions offered.
--ron

 

[Loadmaster]

I do want the last 3 and 12 entries in column K. My first post this morning
showed you the layout of the spreadsheet. What I was looking for did not
work. It seems like the formula you gave me was looking for a larger number
each month, this is not the case. Column K just has the monthly flying
hours, some months are smaller/larger than others. The amount of empty cells
between the monthly figure varies with however many flights I flew that month.

 

[Ron Rosenfeld]

I do want the last 3 and 12 entries in column K. My first post this morning
showed you the layout of the spreadsheet. What I was looking for did not
work. It seems like the formula you gave me was looking for a larger number
each month, this is not the case. Column K just has the monthly flying
hours, some months are smaller/larger than others. The amount of empty cells
between the monthly figure varies with however many flights I flew that month

How did you conclude that the formula I gave you was "looking for a larger
number each month"?

The formula was designed to "look for" the three numeric entries (or 12) in the
highest numbered rows, and it works as designed here.

So please post some real examples of your data, a true copy of what you did
with my formula, and the results you obtained.
--ron

 

[Loadmaster]

Ron, I found what my error was in the formula after playing around with it
for a long while. I had a ) in place of a } and I was putting the word rng
vice inputting the range. Thank-you very much for your help.

 

[Loadmaster]

Ron, I want to place the answer on another spreadsheet within the same
workbook. Where would I insert the name of the worksheet within the formula?