Sum challenge

[Christopher Weaver]

I would like to create a formula in column A that sums the values in column
E starting with the row in which the formula resides and moving upward to,
and including, the first row in column B that is not blank. A static range
won't work as the number of rows above the row with the formula will vary.

Any ideas on this?

Thanks.

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

A20: =SUM(INDIRECT("E"&MATCH(TRUE,(B$1:B20<>""),FALSE)&":E"&ROW()))

Adjust the row reference as needed. For instance, in Row 11:

A11: =SUM(INDIRECT("E"&MATCH(TRUE,(B$1:B11<>""),FALSE)&":E"&ROW()))

Copy up or down as far as needed.

 

[Harlan Grove]

Christopher Weaver wrote...

I would like to create a formula in column A that sums the values in column
E starting with the row in which the formula resides and moving upward to,
and including, the first row in column B that is not blank. A static range
won't work as the number of rows above the row with the formula will vary.

Any ideas on this?

=SUM(INDIRECT("E"&MATCH(FALSE,ISBLANK(OFFSET($B$1,0,0,SUM(ROW()),1)),0)
&":E"&SUM(ROW())))

 

[Christopher Weaver]

Thanks for the quick response.

This doesn't behave like a formula in my Excel. Perhaps there is a problem
with the version I'm using. I'm on 2002 with SP3. Should that present a
problem? I'm pasting your code directly into a cell with only the line
break and '>' at the beginning of the second line removed.

Thanks.

 

[Christopher Weaver]

This is a correction to the previous reply. It does behave as a formula but
it returns an error '#N/A'. The explanation given is that a value is not
available. When I use the analysis tools it points an arrow from cell B:1
to the formula cell.

Thanks again.

 

[JE McGimpsey]

it needs to be array-entered (CTRL-SHIFT-ENTER or CMD-RETURN).

 

[Christopher Weaver]

I didn't get what you meant at first. I've never done anything like this.
But once I cut the formula from the cell, pasted it back in, and hit
CTRL-SHIFT-ENTER, it works, sort of. It gives me a total, but the total is
for the entire column E instead of that portion between the row containing
this formula and the first row above it in which column B has a value.

It appears that the formula you gave me and the formula given by Harlan
Grove both sum cells in the column E that are in rows for which column B is
not blank. I want to start the summation of column E with the first row
that contains a non-blank value in column B and continue down to the row in
which the formula occurs.

Thanks again.

 

[Biff]

Hi!

Try this:

Goto Insert>Name>Define

Name: B (for column B)

In the Refers To box, enter:

=OFFSET(Sheet1!$B$1,,,ROW())

Click Add (to add an additional name)

Name: E (for column E)

Refers to:

=OFFSET(Sheet1!$E$1,,,ROW())

Click OK

Formula for column A:

=SUMPRODUCT(--(B<>""),E)

Biff

 

[Guest]

if you would use a macro this does it - I assume you want to sum up till you
hit an empty row - start with active cell on row you want sum to start (up)
with

Sub Macro1()
ActiveCell.Value = Application.WorksheetFunction.Sum(Range(Cells(Acti
veCell.Row, 5), Cells(ActiveCell.Row, 5).End(xlUp)))
End Sub

 

[Biff]

Hi!

That macro doesn't account for column B being "" or <>"".

Biff

 

[Bob Phillips]

Christopher,

I don't see this behaviour that you describe.

I entered 1,2,3,4,5,...,99 in E1,E2,E3,E3,E5,...,E99

I only entered one value in column B, in B6 (I put 1 as it happens)

I then entered the formula in A8, and I get 21 returned. If I then enter 1
in B5, I get 26. If I then enter 1 in B4, I get 30.

This is exactly as I see your spec.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

my error on misreading the question - nevertheless a simple macro will do the
trick
just a suggestion

Sub Macro1()
'
'lastrow is first blank cell in column b (from the top???)
if cells(1,2).value = 0 then lastrow=1 else lastrow =
cells(1,2).end(xldown).row+1
end if
ActiveCell.Value = Application.WorksheetFunction.Sum(Range(Cells(Acti
veCell.Row, 5), Cells(lastrow, 5).End(xlUp)))
End Sub

 

[Bernie Deitrick]

Christopher,

Your problem statements differ. Now you say:

the first row above it in which column B has a value.

but before you said:

the first row in column B that is not blank.

I took your original to mean that if cell B1 was blank, sum from E2 down to
the current row, no matter if there are lower blanks. But now, if the cell
in column B two rows up is blank, you only want to add together two numbers.

Modify Harlan's formula to this:

=SUM(INDIRECT("E"&MAX(IF(ISBLANK(OFFSET($B$1,0,0,SUM(ROW()),1)),ROW(OFFSET($
B$1,0,0,SUM(ROW()),1)),0))+1&":E"&SUM(ROW())))

Again, entered using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

 

[Christopher Weaver]

You are correct in your interpretation of my request. I don't understand
what I'm doing differently than you. Which formula did you use and how did
you enter it?

 

[Bernie Deitrick]

Christopher,

If B7 is blank, what do you want to be returned in cell A8? 8? If so, then
try the formula I posted.

HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

Christopher Weaver wrote...

This is a correction to the previous reply. It does behave as a formula but
it returns an error '#N/A'. The explanation given is that a value is not
available. When I use the analysis tools it points an arrow from cell B:1
to the formula cell. ....

I wich I had seen JE's formula before I started working on this. Mine
is too fussy about matching in column B.

If I enter =ROW() in each cell in E1:E20, then I enter the following
array formula in A1,

A1:
=SUM(INDIRECT("R"&MATCH(0,-ISBLANK(B$1:B1),0)&"C5:RC5",0))

and fill A1 down into A2:A20, all the formulas in A1:A20 evaluate #N/A
initially because all cells in column B are blank. Then if I enter an X
in cell B5, the formulas in A1:A4 remain #N/A, but A5:A20 now evaluate
to

{5;11;18;26;35;45;56;68;81;95;110;126;143;161;180;200}

There's nothing to sum in A1:A4 since none of the cells in column B on
or above the row of any of these cells are nonblank. Perhaps the
formula should return #NULL! in these cases, in which case change the
formulas to

A1:
=IF(COUNTA(B$1:B1),SUM(INDIRECT("R"&MATCH(0,-ISBLANK(B$1:B1),0)&"C5:RC5",0)),
#NULL!)

 

[Harlan Grove]

duane wrote...

my error on misreading the question - nevertheless a simple macro will do the
trick

....

A macro would do the trick until the values in column B change. Then
the results the macro created could be wrong, but there'd be no clear
way to tell. If you intended for your macro to run every time the
values/entries in column B changed, you should have used Change and
Calculate event handlers. However, if the goal is values in worksheet
cells that adjust automatically as values in other worksheet cells
change, then formulas are FAR & AWAY the better approach.