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
982 Views

Similar Articles

[PageSpeed] 16

"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:

Re: Keeping a SUM filed clear Until All Numbers Entered
Thanks for the helpful replies. Carl -- If you can read this, thank a teacher.... If you are reading it in English, thank a Veteran..... ...

i want excel to show the first tab upon opening, not the second
when i open my spreadsheet, it always opens with the second sheet prominent, rather than the first. how can i change this? Hi, You will need VB for that. Alt+F11 to open VB editor. Double click 'ThisWorkbook' and paste the code below in on the right. For it to work you must enable macros on opening Private Sub Workbook_Open() Application.Goto Sheets(2).Range("A1") End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "robs...

In SUM cells the # sign keeps showing instead of total
AYE! my head... i'm sure it is simple, but i cannot get the SUM cells to show the currency amount. instead, the cells keep showing ####### after i enter the formula. can anyone advise me in how to stop this from happening? what's more annoying is that one SUM cell IS working properly. what have i done? thank you! frustrated student Widen the cell size Regards, Peo Sjoblom "fyremunki" wrote: > AYE! my head... > > i'm sure it is simple, but i cannot get the SUM cells to show the currency > amount. instead, the cells keep showing ####### after ...

Checking for duplication on rows
Hello, In a particular spreadsheet, I would like to verify if the same name is listed on multiple rows. The name is entered in multiple columns on the same row, that is acceptable, but I would like to know if the name is entered on multiple rows regardless of the column. How can I check this? THANKS, karmen -- Karmen ------------------------------------------------------------------------ Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972 View this thread: http://www.excelforum.com/showthread.php?threadid=509495 Presume you're checking some...

Keeping Row 1 At Top When Scrolling?
How do i make it so that the top row, row 1, stays at the top of my sheet no matter how far i scroll down the page? Many thanks Kev. Kev, Select cell A2 and then go to the Window menu and choose Freeze Panes. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "KMc" <mcauley@ourgeneration.freeserve.co.uk> wrote in message news:bf0re3$uoe$1@newsg4.svr.pol.co.uk... > How do i make it so that the top row, row 1, stays at the top of my sheet no > matter how far i scroll down the page? > > Ma...

Count Rows When Using Auto-Filter
Using the auto-filter function I need to count the number of rows returned each time I select a new value. I can do a COUNTA (&#8211;1 to allow for the header row), on the column that counts all the rows but when I use the filter I still get the total count not a count of the reduced quantity by the filter. Can anyone help? Nick use the subtotal function with a first argument of 3 =subtotal(3,A2:A300) as an example. Make sure the column you do the counta on will have values in all rows. -- regards, Tom Ogilvy "Nick" <ner@westnet.com.au> wrote in message news:15...

Ranking/Sorting more than 3 row's
I have a spreadsheet with 742 rows (of company names). I have 5 columns of criteria for the companies. Each column is a different criteria about each company, and has a numerical rank for each company. 1-~75 (some columns/criteria have no rank for a company) I want to create an overall rank of the companies based on how they perform across the 5 rows. I want the companies with the most 1's at the top and then those with 1 & 2's and then those with 2 & 2's etc. Is there a way to sort or rank this spreadsheet of companies? I would do a sum of the inverses of each of...

Hidden Rows
I came accros a spreasheet were the first 8 rows are hidden. I can select and edit the cells by entering the cell reference into th Name Box. But I cannot make them visible - the rows have a height o 15. And selecting all the cells then selecting unhide does not work. Protection has not been applied. Does anyone have any ideas on how this was done? and how can I mak them visible? Thanks -- michael.a ----------------------------------------------------------------------- michael.a7's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3302 View this thread: http:...

Switch row with column?
Hello guys, im very new to excel, and im trying to learn. I cant seem to figure out how to switch my chart around. I have a list of companies on the top, and down the left i have a list of locations. How can i make the locations go on top, and the companies down the left? -- jason57gizmo ------------------------------------------------------------------------ jason57gizmo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30700 View this thread: http://www.excelforum.com/showthread.php?threadid=503627 Depending on the number of rows you're using--since t...

Calculate difference in rows in a query
I have a query that calculates how much revenue is claimed by month per job but then I have to take the new month less the prior month. The problem is the data is in row format. I don't know how to subtract February from January, March from but don't know how. I know how to do it in Excel but not Access. Please help... Example: Order Month JTD Clm Variance 101026521 January $511,525 $0 101026521 February $511,525 $0 101029438 January $1,238 $0 101029438 February $3,713 $2,475 101033168 January $21,465 $0 101033168 February $51,460 $29,995 101034011 Janu...

Formula for vlookup and then sum
Hello. I need a formula to do a vlookup for whenever a certain cost code/cost type appears in column A, then go to column F and sum the dollar amounts. Help please :) Hi, You can only use full columns in E2007 for E2003 and earlier define a shorter range =SUMPRODUCT((A:A="Your cost code")*(F:F)) Mike "Alberta Rose" wrote: > Hello. I need a formula to do a vlookup for whenever a certain cost > code/cost type appears in column A, then go to column F and sum the dollar > amounts. Help please :) 2003 use array formula { =SUM((A1:A10...

How to open a blank form for adding a new record
Hello, I have a form for adding/viewing employee records. I can open the form, view records and using the navigation button, add a new record just fine. However, I'd like to open the form using a command button so that all fields are blank and a new record can immediately been added without having to use the navigation button. Can anyone help me figure out how to do this? TIA, Rich On Tue, 13 Nov 2007 13:20:02 -0800, rich wrote: > Hello, > > I have a form for adding/viewing employee records. I can open the form, > view records and using the navigation button, add a...

Insert blank rows
Rather than using the control button to select every other row, which can be very time consuming when you have more than 100 rows, I am trying to figure out how to auto-insert blank rows between pre-populated rows of information. Deb, Try this macro. ALT+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code in on the right . Change the sheet name to your sheet and run the code Sub insert_rows() Dim ws As Worksheet, x as long Set ws = Sheets("Sheet3") lastrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row For x = ...

return to first button click event after another button click event
Hi, Is it possible to - after clicking a command button on a form and running some code to then exit that button click event and wait for a user to click another command button, this then returning code execution to the first command button click event at the point were it left from ? regards Trevor. -- Message posted via http://www.accessmonster.com On Fri, 04 Dec 2009 05:31:08 GMT, "trevorC via AccessMonster.com" <u44860@uwe> wrote: >Hi, >Is it possible to - after clicking a command button on a form and running >some code to then exit that button...

Help with counting rows
I have a datafeed that comes to me everyday via email , every day the feed had slighty different rows as it has different updated products i.e. somedays 999 , 1001 etc. What i need to do is run a macro so when the excel file arrives to count the number of rows and then add the letter "y" in a 3 new columns . At the moment i have to do it manually is this easy to do Paul iLastRow = Range("A1").End(xlDown) Range("B1").Resize(iLastRow,3).Value = "y" -- HTH Bob Phillips (remove nothere from email address if mailing direct) <plm11111@h...

Insert a row based on a macro
Is there a way to insert a row based on a formula. For example, If A5=25, I then want to duplicate the existing row and insert it in the line below. See answers in your other post. Pls don't multi post -- Don Guillett SalesAid Software donaldb@281.com "Mindie" <Mindie@discussions.microsoft.com> wrote in message news:75A1DDD5-6334-410D-8646-091B43D1B198@microsoft.com... > Is there a way to insert a row based on a formula. For example, If A5=25, I > then want to duplicate the existing row and insert it in the line below. ...

insert 'x' number of rows in excel
Hi, I'm looking to insert 'x' no. of rows into an excel document where 'x' is the result of a 'countA' function. Is this possible? Hi# only possible with VBA. Would this be feasible for you? >-----Original Message----- >Hi, >I'm looking to insert 'x' no. of rows into an excel document where 'x' is >the result of a 'countA' function. > >Is this possible? >. > Yes VBA is feasible "Frank Kabel" wrote: > Hi# > only possible with VBA. Would this be feasible for you? > > >-----Original ...

Insert row, table to table
Hi, As I cannot use INSERT INTO with values specified to WHERE. I have created an interim temporary table where the record contains two text strings.Now I want to be able to take those text strings and append to the correct table where the record is set by an ID number. First_Name = Fred Last_Name = Jones Its a new contact so it is too be added to a customer with an ID = 1375 Now I can't use the following sql statement strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _ & " VALUES (""" & strFirst & "&...

Sum by month and year
I am trying to get daily totals sumed and put into a monthly worksheet. I have been using SUMIF comparing the month and year of each payment. Here is the formula. =SUMIF(MONTH(Payments!b7:b3000)&YEAR(Payments!b7:b3000), MONTH(B39)&YEAR(B39),Payments!F7:F3000) Excel is saying there is an error but when I look art the edit formula area is seems to calculating correctly. Can you help me? Steve You can use sumproduct without the CSE =sumproduct((month(dr)=month(b39))*year(dr)=year(b39))*sumrng) -- Don Guillett SalesAid Software donaldb@281.com "Steven Robilard" <stev...

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. "Tami" <Tami@discussions.microsoft.com> wrote in message news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com... > can anyone write the formula to ...

How do I delete pages in Excel? Keeps printing blank pages at end.
I'm printing charts in Excel. Some of them are adding blank pages at the end of the charts. How do I get rid of these? Several ways: 1) Change the location of the chart to have its own sheet. Do this by right clicking the chart, go to "Location" and putting it on it's own sheet. Then you should be able to print it by itself without any white stuff. 2) Make sure that the page breaks are defined well. Go to View -> Page Break Preview . From there, you can move around the blue lines to determine your page breaks. Then, when you're printing it, make sure that you c...

Sum From Beginning of Column #2
How do I create a formula that adds from the top of a column? Here's the situation. I have a column of numbers (with a header of "Pay"). To the right of that colum I want to keep a running total ("Yearly Total"). Every time I get paid, I'm going to enter the amount in the pay column and then I want the Yearly Total column to automatically add it. When I create a fomula (say =SUM(B2:B21)), and propigate it down, it doesn't start at the top. In others words the next formula is =SUM(B3:B22). I want it to be =SUM(B2:B22). And, if a value is not filled in on t...

Array Formula isn't summing but counting
I need to sum information based on several other criteria -- accoun number, sort codes, company names. The array formul {=SUM(A1:F25="A")*(E1:E25>0))} returns a count rather than a sum Does anyone know of a way get it to add my data rather than countin how many? Staci -- Message posted from http://www.ExcelForum.com Hi if you want to sum column E try the array formula =SUM((A1:F25="A")*(E1:E25>0)*(E1:E25)) or a non array formula =SUMPRODUCT((A1:F25="A")*(E1:E25>0),E1:E25) -- Regards Frank Kabel Frankfurt, Germany > I need to sum information ...

Moving Column Sums to Rows
What is the simplest way to enter a sequence of values such as =SUM (A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN? What is the simplest way of converting simultaneously such values to ($a$1:$a$100) etc.? I could only find a slow and clumsy method by putting the formula in a row, right dragging, then using F4 on each individual cell before using paste special with transpose. Roger PB Hi Roger, > What is the simplest way to enter a sequence of values such as =SUM > (A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN? > =SUM(OFFSET($A$1,0,ROW()-1,1,100...

OUtlook 2003 is sending blank replies to emails I haven't seen
Intermittently, my outlook will send blank replies to emails that I haven't even seen yet, yet alone opened. I get messages from commercail and personal emials asking what I meant to send? Any help out there? What type of email account? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT....