Offset with Blank Rows?

  • Thread starter Thread starter Blake
  • Start date Start date
B

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?
 
hi Blake,

array formula to validate with ctrl + shift + enter

=SUM(INDIRECT("A2:A"&MAX(IF(A2:A65535<>"",ROW(A2:A65535)))))
 
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.
Click to expand...

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


Regards
Claus Busch
 
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
Click to expand...

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.
 
=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).
 
=SUM(INDIRECT("A"&LARGE(IF(ISBLANK(A2:A10000),0,ROW(2:10000)),
10)):A10000)
Click to expand...

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.
 
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.
Click to expand...

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


Regards
Claus Busch
 
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
Click to expand...

Thank you Claus. It works perfectly.
 
Thank you Claus.  It works perfectly.
Click to expand...

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"))
 
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?
Click to expand...

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


Regards
Claus Busch
 
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
Click to expand...

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 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.
Click to expand...

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.
 
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.
Click to expand...

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


Regards
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))
Click to expand...

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


Regards
Claus Busch
 
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
Click to expand...

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