Offset with Blank Rows?

[Blake]

At the top of the column, I want to sum the last ten entries ignoring
blank cells. Such as:

$3,034.70

$255.10
$285.50
$339.80


$335.70
$302.70
$304.00
$268.40
$276.90
$264.90
$401.70

Every day there is a new entry, and at the top of the column I just
want the sum of the last ten entries ignoring the blanks. Do I use
the offset function for this?

 

[isabelle]

hi Blake,

array formula to validate with ctrl + shift + enter

=SUM(INDIRECT("A2:A"&MAX(IF(A2:A65535<>"",ROW(A2:A65535)))))

 

[Claus Busch]

Hi Blake,

Am Tue, 24 May 2011 09:55:09 -0700 (PDT) schrieb Blake:

At the top of the column, I want to sum the last ten entries ignoring
blank cells.

array formula (CRTL+Shift+Enter):
=SUM(INDIRECT("A"&LARGE(IF(ISBLANK(A2:A10000),0,ROW(2:10000)),10)):A10000)


Regards
Claus Busch

 

[Blake]

Hi Blake,

Am Tue, 24 May 2011 09:55:09 -0700 (PDT) schrieb Blake:


array formula (CRTL+Shift+Enter):
=SUM(INDIRECT("A"&LARGE(IF(ISBLANK(A2:A10000),0,ROW(2:10000)),10)):A10000)

Regards
Claus Busch

Claus and Isabelle. Thank you. I could not get either formula to
work correctly. Claus's formula works if there is only one blank row
and the data is inputted directly, however if the data is a result of
a formula it doesn't work. Isabelle's formula did not come up with
the correct answer when I added data to the end of the column.

In some instances there are multiple blank rows.

 

[Blake]

=SUM(INDIRECT("A"&LARGE(IF(ISBLANK(A2:A10000),0,ROW(2:10000)),
10)):A10000)

I can get this formula to work in a practice spreadsheet, but not in
my original spreadsheet. (It returns a 0 value.)


=SUM(INDIRECT("A2:A"&MAX(IF(A2:A65535<>"",ROW(A2:A65535)))))

This formula adds up all the numbers in the column, but it doesn't
seem add up only the last ten entries (excluding blanks).

 

[Blake]

=SUM(INDIRECT("A"&LARGE(IF(ISBLANK(A2:A10000),0,ROW(2:10000)),
10)):A10000)

OK. I've been working on this all day. I just got this formula to
work by using absolute values rather than values derived from a
formula.

 

[Claus Busch]

Hi Blake,

Am Tue, 24 May 2011 18:02:15 -0700 (PDT) schrieb Blake:

OK. I've been working on this all day. I just got this formula to
work by using absolute values rather than values derived from a
formula.

try:
=SUM(INDIRECT("C"&LARGE((C2:C10000<>"")*ROW(2:10000),10)&":C10000"))
CRTL+Shift+Enter


Regards
Claus Busch

 

[Blake]

Hi Blake,

Am Tue, 24 May 2011 18:02:15 -0700 (PDT) schrieb Blake:


try:
=SUM(INDIRECT("C"&LARGE((C2:C10000<>"")*ROW(2:10000),10)&":C10000"))
CRTL+Shift+Enter

Regards
Claus Busch

Thank you Claus. It works perfectly.

 

[Blake]

Thank you Claus.  It works perfectly.

Final Question:

How would I change this formula so that it now counts blank rows
instead of skipping them?

=SUM(INDIRECT("ap"&LARGE((AP14:AP10000<>"")*ROW(14:10000),
10)&":ap10000"))

 

[Claus Busch]

Hi Blake,

Am Wed, 25 May 2011 10:09:46 -0700 (PDT) schrieb Blake:

How would I change this formula so that it now counts blank rows
instead of skipping them?

blank rows in used range?
=COUNTBLANK(INDIRECT("AP14:AP"&MATCH(0,AP:AP,-1)))


Regards
Claus Busch

 

[Blake]

Hi Blake,

Am Wed, 25 May 2011 10:09:46 -0700 (PDT) schrieb Blake:


blank rows in used range?
=COUNTBLANK(INDIRECT("AP14:AP"&MATCH(0,AP:AP,-1)))

Regards
Claus Busch

I misspoke. I want to count the last ten rows INCLUDING the blank
rows instead of skipping the blank rows and going to the next row with
data.

 

[Blake]

I misspoke.  I want to count the last ten rows INCLUDING the blank
rows instead of skipping the blank rows and going to the next row with
data.

I want to SUM the last ten rows of data including the blank rows. So
if 8 of the last ten rows have data and two are blank, I want to sum
those 8 rows. Ten total rows--two are blank--eight have data.

 

[Claus Busch]

Hi Blake,

Am Wed, 25 May 2011 11:03:49 -0700 (PDT) schrieb Blake:

I want to SUM the last ten rows of data including the blank rows. So
if 8 of the last ten rows have data and two are blank, I want to sum
those 8 rows. Ten total rows--two are blank--eight have data.

sorry, that I missunderstood.
Try:
=SUM(OFFSET(Tabelle1!$AP$1,MATCH(0,Tabelle1!AP:AP,-1)-10,,10))


Regards
Claus Busch

 

[Claus Busch]

Hi Blake,

Am Wed, 25 May 2011 20:10:03 +0200 schrieb Claus Busch:

=SUM(OFFSET(Tabelle1!$AP$1,MATCH(0,Tabelle1!AP:AP,-1)-10,,10))

change the formula to:
=SUM(OFFSET($AP$1,MATCH(0,$AP:$AP,-1)-10,,10))


Regards
Claus Busch

 

[Blake]

Hi Blake,

Am Wed, 25 May 2011 20:10:03 +0200 schrieb Claus Busch:


change the formula to:
=SUM(OFFSET($AP$1,MATCH(0,$AP:$AP,-1)-10,,10))

Regards
Claus Busch

Thank you, Claus. That's exactly what I wanted. (Now I can go back
into hiding.)