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 |

12/26/2009 3:11:01 PM

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

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 |

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 |

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 |

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 |

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 |

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 |

12/26/2009 8:11:02 PM

Thanks for the helpful replies. Carl -- If you can read this, thank a teacher.... If you are reading it in English, thank a Veteran..... ...

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...

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 ...

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...

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...

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 (–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...

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...

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

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...

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...

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...

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...

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 = ...

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...

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...

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. ...

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 ...

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 & "&...

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...

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 ...

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...

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...

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 ...

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...

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....