Summing with Spaces
 

I have a column of data separated with a space. Each space represents
a new day.

2
3
9 14

4
4 8

5
6
7
7 25

4
19 23


Is there a formula I can put along side in column b that will total
each day's results and show it like I've done above?

Thanks

Summing with Spaces
 

This works but it requires the cell immediately above your data be empty.

This also assumes that the next cell after the last entry in the range is
empty.

So, assume cell A1 is empty and your data starts in cell A2.

Enter this formula in B2 and copy down as needed:

=IF(A3="",SUM(A2:INDEX(A$1:A1,LOOKUP(2,1/(A$1:A1=""),ROW(A$1:A1)))),"")

Here's a screencap:

http://img519.imageshack.us/img519/1010/sumcs3.jpg

--
Biff
Microsoft Excel MVP


"Peter" wrote in message
...
I have a column of data separated with a space. Each space represents
a new day.

2
3
9 14

4
4 8

5
6
7
7 25

4
19 23


Is there a formula I can put along side in column b that will total
each day's results and show it like I've done above?

Thanks

Summing with Spaces
 

This works with the data starting in row 1

=IF(A2="",SUM($A1:INDEX($A$1:A1,MAX(1,MAX(IF($A$1: A1="",ROW($A$1:A1)))))),"")

this is an array formula, so Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Peter" wrote in message
...
I have a column of data separated with a space. Each space represents
a new day.

2
3
9 14

4
4 8

5
6
7
7 25

4
19 23


Is there a formula I can put along side in column b that will total
each day's results and show it like I've done above?

Thanks

Summing with Spaces
 

I should have mentioned that this formula starts in row 2.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Bob Phillips" wrote in message
...
This works with the data starting in row 1

=IF(A2="",SUM($A1:INDEX($A$1:A1,MAX(1,MAX(IF($A$1: A1="",ROW($A$1:A1)))))),"")

this is an array formula, so Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Peter" wrote in message
...
I have a column of data separated with a space. Each space represents
a new day.

2
3
9 14

4
4 8

5
6
7
7 25

4
19 23


Is there a formula I can put along side in column b that will total
each day's results and show it like I've done above?

Thanks

Summing with Spaces
 

Actually, I had to put it into row one to make it work. Thanks a lot.
Much appreciated.

On Wed, 12 Dec 2007 10:33:40 -0000, "Bob Phillips"
wrote:

I should have mentioned that this formula starts in row 2.