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.
0
Utf
12/26/2009 3:11:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
990 Views

Similar Articles

[PageSpeed] 22

"Tami" <Tami@discussions.microsoft.com> wrote in message 
news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com...
> 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: news@netfront.net ---
0
Utf
12/26/2009 3:41:16 PM
=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.
0
Utf
12/26/2009 3:50:01 PM
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.
0
Utf
12/26/2009 4:12:01 PM
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" <Tami@discussions.microsoft.com> wrote in message 
> news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com...
> > 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: news@netfront.net ---
> .
> 
0
Utf
12/26/2009 5:39:01 PM
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
<Tami@discussions.microsoft.com> 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" <Tami@discussions.microsoft.com> wrote in message 
>> news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com...
>> > 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: news@netfront.net ---
>> .
>> 

0
Lars
12/26/2009 5:51:26 PM
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" <Tami@discussions.microsoft.com> wrote in message 
news:FC66D86C-CC0F-4750-91B2-CAB677E873C8@microsoft.com...
> 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" <Tami@discussions.microsoft.com> wrote in message
>> news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com...
>> > 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: news@netfront.net ---
>> .
>> 


0
T
12/26/2009 6:00:28 PM
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
> <Tami@discussions.microsoft.com> 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" <Tami@discussions.microsoft.com> wrote in message 
> >> news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com...
> >> > 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: news@netfront.net ---
> >> .
> >> 
> 
> .
> 
0
Utf
12/26/2009 8:11:02 PM
Reply:

Similar Artilces:

updatable and changable row source
In my form that contain one text box (unbound) at the top and one list box in down, when I start the typing in the text box the row source of list box that is the query that depend on the text box (parameter) automatically updated and show the records in the list box. Best regards. Masoud wrote: >In my form that contain one text box (unbound) at the top and one list box in >down, when I start the typing in the text box the row source of list box that >is the query that depend on the text box (parameter) automatically updated >and show the records in the list box. Tha...

Change Row Fill Color
I'm having a problem that I'm pretty sure can only be resolved with some type of Macro. I would like to know if it is possible to change the fill color of an entire row when text is entered in a specific cell within that row. For example. When "Not Received" is entered in cell C3, I would like all of row 3 to be filled with light blue. If something else is entered in that same cell (e.g. "Received"), I do not want the fill color to change at all. I know it's possible to change a single cell this way using Conditional Formatting, but how do you apply...

Moving data in a large cell range to one row
I have data in cell range A1:R34. All of the data needs to be in column A. Instead of cutting and pasting each row or column in the range, is there a way to move all of this data quickly to column A? Thanks for the help. Hi and how do you want it to move into this column?. Best would be you give an example -- Regards Frank Kabel Frankfurt, Germany "Todd" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:1b3101c4a270$9123dce0$a301280a@phx.gbl... > I have data in cell range A1:R34. All of the data needs > to be in column A. Instead of cutting and ...

How to round & sum only the displayed (rounded) numbers
I have rounded some numbers to the thousands digits. I need help finding a formula that rounds the displayed (rounded thousands) digits. Everything I try only calculates it based on the unrounded numbers and as such many sum numbers are off by 1 (or really 1,000). Any help would be awesome! thanks On Fri, 19 Feb 2010 06:57:01 -0800, ALG <ALG@discussions.microsoft.com> wrote: >I have rounded some numbers to the thousands digits. I need help finding a >formula that rounds the displayed (rounded thousands) digits. Everything I >try only calculates it based on t...

Adding SOP number to POP blank form
I'd like to know how to add the SOP number to the POP form in GP 8.0. I realize in the earlier version you were able to configure the SOP_POPLink table relationships to POP_PO however I can't seem to configure this in 8.0. Is there a new report writer function? Thanks, ...

print heading row on every page
I followed the instructions for getting a heading row to repeat on every page, but I couldn't get it to work. In order to have rows repeat on every page, Go to File/Page Setup/Sheet and select the rows to repeat at top. "Betty" wrote: > I followed the instructions for getting a heading row to repeat on every > page, but I couldn't get it to work. > > Hmm....be more specific on what are you getting. Anyway... File -> Page Setup -> Sheet -> Print title: "Rows to repeat at top" Betty Wrote: > I followed the instructions for gett...

insert row when cell full, auto wrap
Hi there, I used this code in ver 2003 to insert a row and wrap text in a cell..BUT, it does not work in ver 2007. Any ideas? Much appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ma.MergeCells = False c.ColumnWidth = ...

Adding row to a table
I have appreciated the input of g. mayor and have found his "An alternative method of adding a row to a protected table" (http://www.gmayor.com/word_vba_examples.htm) to be quite helpful. It is designed to be an 'exit macro' and I am trying to use it in a slightly different way. My issue is that as a user tabs through a form they will always come to the last cell and tabbing to the next formfield just creates another row. So I am attempting to set up a button (commandbutton) that will run the macro and add an additional row 'on demand.' I have this...

VLOOKUP and highlight row
Hi gang, So I have a spreadsheet with 20,000 items listed by UPC code. The top 5 rows are in freeze pane mode with some direction as how to use, in cell B3 I have a empty cell where the user enters a UPC number, the cell next to it has a vlookup function that finds the item and returns the wholesale cost found in column 6 - easy enough. =VLOOKUP(B3,A6:J20012,6,FALSE) I would like it IF when vlookup finds the UPC code to highlight the entire row because the user may need to look at other data. As if you were to scroll down manually, find the row and highlight with a mouse click...

X and Z reports come up blank for shifts.
Does anyone have an idea why X & Z reports would come out blank, When we know for sure that there was activity in the store. This happen to one of our clients before, but we were never able to replicate the error to figure out why this happen in the first place. Any ideas would be helpful thx cj Blanks usually mean that no template was selected for reports in the Receipt Format properties. I had one where a reprot printed but all the values were zero - that was a different issue, but I can't remember what it was - I think we just deleted the receipt format and started over, bu...

Converting a column of information into a row of information
I was curious if anyone knew of a way to convert a column into a row Essentially we receive a spreadsheet a few times a month that has a fe columns of information. Our database however can only accept cs uploads and they have to follow a very strict template. In order t properly upload the data that we are receiving I would need to turn columns into rows (or optimally into a single cell with each piece o data from that column separated by a comma). Our template works as follows. We have rows for contact name, address phone number, ect... At the end of the spreadsheet there are 3 column in w...

copy a row of data to a column in another workbook?
I am trying to get the data i enter into my charity subscription workbook, to be automatically updated in my gift aid register (workbook) so that i do not have to input twice. The data is entered into the subs register in rows and the gift aid register is entered in columns. Any ideas would be appreciated. hi, =('H:\CodeStuff\[ConnectPaths.xls]Sheet1'!D4) a formula like this in your gift aid register wb will draw the data over from your charity subscription wb. I don't know your file paths so you will have to change it to fit your set up. in the gift aid register wb, put the ...

How do I unhide The first column or row
After hiding the first column or row in Excel, how do I unhide it One way: Enter A1 in the Name Box on the left side of the formula bar and type Enter. Choose Format/Row/Unhide or Format/Column/Unhide In article <26178EB3-8474-4C26-9087-75487987BBFB@microsoft.com>, ashekumar <ashekumar@discussions.microsoft.com> wrote: > After hiding the first column or row in Excel, how do I unhide it Another way - click in the column B label and drag into the empty box above the row numbers, then right-click and choose unhide same process to unhide the first row, but drag fr...

Selecting Many Non-Adjacent Rows Easily
In a spreadheet with 100's of rows, how do I easily or automatically select non-adjacent rows at a constant interval, eg every 5 rows of an 800 row sheet? Hi, Assign this macro to a keyboard shortcut. Sub jumpLines() jumpLine = 5 Selection.Offset(jumpLine, 0).Select End Sub jeff >-----Original Message----- >In a spreadheet with 100's of rows, how do I easily or automatically select non-adjacent rows at a constant interval, eg every 5 rows of an 800 row sheet? >. > What are you looking to achieve? It may be that there are far easier ways of doing what you want. -- ...

how do i carry a balance to next row without it continuing down?
i have made a worksheet that adds and subtracts across the row, each row is a month, then the balance in the last colunm jumps down to the next row (month)first column. everything works great but i don't want the balance for the next month continuing down to the each month same for the ending balance. what i want is to have it end each time with just the beginning balance for the coming month, then as i add a percentage rate it will add across the row and put the ending balance in the next row (month) i hope this make sense ...

Lookup Values, etracting Row header and column header.
Hi all, I have a dat matrix having 100 rows and 100 columns. The data is dynamic and keeps changing. 3500 3550 3600 3650 3700 3550 - - - - 3600 - - - - 3650 - - - - 3700 - - - - 3750 - - - - 3800 - 20 - - 3850 - - - - 3900 - - - 15 For eg: the data in row 3800 and column 3600 shows a value of 20. Is there any function by which i can extract any positive values in the data matrix such that it gives a summary in another worksheet: # Row Column Value 1 3800 3600 20 2 3900 3700 15 Note: th...

Lookups and Offsets
I would like to take a previous issue to the next level of complexity. Here's the scenario -- Message posted from http://www.ExcelForum.com Here's a possible solution :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "wmjenner >" <<wmjenner.110p7g@excelforum-no...

Moving spreadsheet rows with photos Problems in Excel 2007
I have an inventory worksheet in Excel 2002. There is a photo in one cell in each row that needs to move with the row. I formatted each photo to move and size with cell, and had no problems. Have just had to change to Excel 2007. I can move the row by cut and insert cut cells, and the photo moves with it. BUT the problem comes with the rows above the cut cells - the photos move down into the blank row left by the cut row when moving to another sheet- so the photos left are not with their original rows and descriptions. I have looked at all the options that I can find (steep learning...

Ability to insert a row to the Payroll Quick Entry
I would like to make the following suggestion with respect to the Payroll Canada. I was wondering if the functionality in the Payroll Qiuck Entry could allow the user to insert a row when entering hours. Presently we have the ability to delete a row but not insert a row. If you are making adjustments to an individual employee you can't insert a row you have to do the adjustments at the bottom of the page. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree&...

Sum of 6 moving cells
Good day to all, Sheet1: Column A is the date (from Jan 1, 2005 to the current date), Col B contains the hours worked on that date and Col C is hours worked for the last 12 days. I manually type in the hours worked everyday on Col B and there is a formula on Col C to add the last 12 days. There's about 45 units that I keep track of the hours worked. How can I show on a summary sheet, the hours worked of each unit as of yesterday? My problem is that the cell that totals the last 12 days moves down one cell everyday. -- ledzepe --------------------------------------------------------...

Sum and DateDiff
Hi, I doing a calculation in a query of the difference in times. I'm wondering if I can control the decimal precision to be just (2) places. The number I end up with is ie; 5.6666666666, can I have this to round off to something like 5.67? Here is the query expression I'm using for this; TotalTime: Sum(DateDiff("n",[StartTime],[EndTime])/60) Thanks you, On Sat, 5 Jun 2010 19:33:30 -0700, Kelly <Kelly@discussions.microsoft.com> wrote: >Hi, >I doing a calculation in a query of the difference in times. I'm wondering >if I can control t...

Counting Rows
Hello, I am trying to do the following: 1. Count populated Rows using VB 2. If the # of populated rows is less than 2 post a message box dsiplaying the count 3. If the # of populated rows is greater than 3, continue on with the rest of the macro. Anything you can do to hlep is greatly appreciated. This will look at the last populated cell in the desired row. Sub countrowsincolA() mc = 2 '"a" x = Cells(Rows.Count, mc).End(xlUp).Row If x < 2 Then MsgBox "Only " & x Else 'goon MsgBox "oh boy" End If End Sub -- Don Guillett Microsoft MVP Excel S...

GL Account on POP Blank Form
I am running Dynamics 8.0, and I am want the GL account number to show up on each line item. I found the article on CustomerSource telling me how to do it, but I keep getting error messages when trying to run the report. I noticed that the article applies to GP v. 7.0. Does anyone know if the procedure is different for v. 8.0 and where I can find those instructions? We're showing the GL Account on the POP Blank Form in 8.0 and I believe we followed the same article. I don't think there have been any changes. If you'll post the error message I'll try to help. ...

Sum errors in Performance Investments reporting
A couple or errors have been introduced into the reporting section of Money 2000 Deluxe. These are summing errors that do not show in any accounts. They corrupt the total value of investments, realized gains/loss and percentage of gains. Is there any way to locate this erroronous data and eliminate it? ...

Ignore Blank
Hi to all, I have a range name that might have blank cells. I used it in a "validating data" settings. I hade a sourse=rangeName and also have chose the "list" option. The "Ignore Blank" is selected. When I use this list I can see the Blank cells!! Why this option is not working ???? You misunderstand the purpose of "ignore blanks" Blank Cells in Source List If the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message. To turn prevent this: Select the cell that contains a d...