Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable and summing/not summing | Excel Discussion (Misc queries) | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
Spaces | Excel Worksheet Functions | |||
spaces not recognized as spaces | Excel Worksheet Functions | |||
How do I add leading spaces to a value? | Excel Worksheet Functions |