Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, sum down to the first blank row
can anyone write the formula to sum down to the first blank row it encounters?
In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, sum down to the first blank row
"Tami" wrote in message ... can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. Try this formula in cell B1: =SUM(OFFSET(B2,,,MATCH(TRUE,ISBLANK(B2:B100),0),)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Ã…ke --- news://freenews.netfront.net/ - complaints: --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, sum down to the first blank row
=SUM(B2:INDEX(B2:B100,MATCH(TRUE,INDEX(B2:B100="", ),)))
"Tami" wrote: can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, sum down to the first blank row
Wonderful!...thank you...As i added lines at the end of the range, it picked
them up. now, can you make the formula flexible if i insert a row at the beginning of the range?....so the formula needs to know to always start the immediate row beneath it. do we use offset? "Teethless mama" wrote: =SUM(B2:INDEX(B2:B100,MATCH(TRUE,INDEX(B2:B100="", ),))) "Tami" wrote: can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, sum down to the first blank row
ok, i'll try it. By chance, will it address my reply to teethless mama "what
happens when i insert a row at B2?", will your forumula pick it up? thanks:-) "Lars-Ã…ke Aspelin" wrote: "Tami" wrote in message ... can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. Try this formula in cell B1: =SUM(OFFSET(B2,,,MATCH(TRUE,ISBLANK(B2:B100),0),)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Ã…ke --- news://freenews.netfront.net/ - complaints: --- . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, sum down to the first blank row
With some changes the case when you insert new a new row between rows
1 and 2 can be handled. Try this: =SUM(OFFSET(B1,1,,MATCH(TRUE,ISBLANK(OFFSET(B1,1,, 100)),0))) Still an array formula. Hope this helps / Lars-Åke On Sat, 26 Dec 2009 09:39:01 -0800, Tami wrote: ok, i'll try it. By chance, will it address my reply to teethless mama "what happens when i insert a row at B2?", will your forumula pick it up? thanks:-) "Lars-Åke Aspelin" wrote: "Tami" wrote in message ... can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. Try this formula in cell B1: =SUM(OFFSET(B2,,,MATCH(TRUE,ISBLANK(B2:B100),0),)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke --- news://freenews.netfront.net/ - complaints: --- . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, sum down to the first blank row
Try this array formula** :
=SUM(INDIRECT("B2:B"&MATCH(TRUE,INDIRECT("B2:B100" )="",0)+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Adjust for a reasonable end of range B100. Note that immediately after you insert a new row 2 cell B2 will be empty and is therefore the first empty cell in the referenced range. So, the sum will show 0 until you enter something in cell B2. -- Biff Microsoft Excel MVP "Tami" wrote in message ... ok, i'll try it. By chance, will it address my reply to teethless mama "what happens when i insert a row at B2?", will your forumula pick it up? thanks:-) "Lars-Åke Aspelin" wrote: "Tami" wrote in message ... can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. Try this formula in cell B1: =SUM(OFFSET(B2,,,MATCH(TRUE,ISBLANK(B2:B100),0),)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke --- news://freenews.netfront.net/ - complaints: --- . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset, sum down to the first blank row
Thank you both...they both worked!
tami "Lars-Ã…ke Aspelin" wrote: With some changes the case when you insert new a new row between rows 1 and 2 can be handled. Try this: =SUM(OFFSET(B1,1,,MATCH(TRUE,ISBLANK(OFFSET(B1,1,, 100)),0))) Still an array formula. Hope this helps / Lars-Ã…ke On Sat, 26 Dec 2009 09:39:01 -0800, Tami wrote: ok, i'll try it. By chance, will it address my reply to teethless mama "what happens when i insert a row at B2?", will your forumula pick it up? thanks:-) "Lars-Ã…ke Aspelin" wrote: "Tami" wrote in message ... can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. Try this formula in cell B1: =SUM(OFFSET(B2,,,MATCH(TRUE,ISBLANK(B2:B100),0),)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Ã…ke --- news://freenews.netfront.net/ - complaints: --- . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Excel 2002: Return blank when VLOOKUP on blank cells | Excel Discussion (Misc queries) | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
OFFSET to include blank cells | Excel Worksheet Functions |