MrExcel Publishing
Your One Stop for Excel Tips & Solutions

SUM to empty space


Posted by Walter on November 18, 2000 4:39 PM

Hi,
I would like to add up =SUM(B1: to the empty space).
The empty space on B5 can move to B6 or B3.
Is it possible to make a formula where it finds
the empty space and adds up whats above it.

Thanks a lot
Walter

Examples:
A B
1 10
2 25
3 12
4 77
5 <--- Empty space
6 Total =124
7

--------------------------------
A B
1 10
2 25
3 12
4 <--- Empty space
5 Total = 74
6
7


Posted by Aladin Akyurek on November 19, 2000 12:56 AM

As I understand it, both your formula for summing and the values that you want to sum are in the same column, separated by an empty space (in fact an empty row). Apparently you are inserting a new row after the last value to enter a new value and you are deleting a row to remove a value that the row contains. If this is the right description,the following does what you want:

B1 contains 10, B2 contains 25, and B3 contains 12 (from your example). Then:

Put in B5 the formula

=SUM(INDIRECT(D1))

Put in D1 the formula

=CONCATENATE(ADDRESS(ROW(B1),COLUMN(B1)),":",E1)

and put in E1 the formula

=ADDRESS(ROW(B5)-2,COLUMN(B5))

In all frankness, I have doubts about this kind of convoluted solutions and the problems they solve.

Cheers.

Aladin

Posted by WALTER on November 20, 2000 2:56 AM

Hi Aladin,
Thanks for your reply.
What I was trying to is to have the formular with the total of "74" which is the total of 10+12+25. This formular could be at C1 for example reading something like: =SUM(B1: to the empty space)
I need it to find the empty space because sometimes there are more or less numbers to add up.

Thanks a lot for your help
Walter

Posted by Aladin Akyurek on November 20, 2000 9:09 AM


You noticed of course that I avoided your construct "to the empty space" in my first reply (which is difficult to compute). I avoid it again in the suggestion below. Hope though that it works for you.

If the column that contains the numbers to be totaled contains no formulas like SUM, AVERAGE, COUNT that return aggregate values which are based the numbers to be totaled, I would suggest using in C1

=SUM(B:B) where B is the column you reserve to hold the numbers you want to sum, etc.

Aladin

Posted by Loren on November 20, 2000 9:48 AM


Another description is to sum from the 1st number
to the last in a range. If your formula is in the
cell to the left of the range, enter
=sum(Rightarrow CtrlShiftRightarrow).
This covers the contiguous range.

Posted by Tim Francis-Wright on November 20, 2000 10:23 AM

Hi,

As long as column B has nothing in it but
the numbers that you want to add, try this in
a cell in some other column (it's an array formula,
so use control-shift-enter to input it):

=SUM(INDIRECT("B1:B" & MIN(IF(ISBLANK(B1:B10000),ROW(B1:B10000)))))

HTH