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.
DaveCoz
excel
39879 articles. 
2 followers.
3 Replies
595 Views
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 SalesAid Software dguillett1@austin.rr.com "Dave Coz" <DaveCoz@discussions.microsoft.com> wrote in message news:26F3BF9E-F71E-4655-8031-C5C6429B544C@microsoft.com... > 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.
dguillett1You mean the number of used rows in a single column?
dim myNum as long
mynum = application.counta(worksheets("Somesheetnamehere").range("a:a"))
if mynum < 2 then
msgbox mynum
'exit sub '???
else
rest of macro here
end if
I figured you meant greater than or equal to 3. If you didn't, what happens at
3?
Dave Coz wrote:
>
> 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.
--
Dave Peterson
petersodHi,
You might try
Dim x As Long
x = WorksheetFunction.CountA(Range("A:A"))
If x < 2 Then
MsgBox x & " rows have data."
End If
if you want to exit the routine if x<2 then add a line after MsgBox the reads
Exit Sub
If you just want the message box then
Dim x As Long
x = WorksheetFunction.CountA(Range("A:A"))
If x < 2 Then MsgBox x & " rows have data."
these assume the column A is the one you are checking.
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire
"Dave Coz" wrote:
> 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.
ShaneDevenshire
Similar Artilces:
FRx CALC row
I'm trying to construct an FRx report that calculates a fixed percentage of a GL Acct, where the percentage changes depending on the base period. For example, let's say I want the percentage to be 30% for the first 6 months, and 50% for the last 6 months. How can I get the MTD amount for the base period, as well as the YTD amount, to be correct? If we assume that an expense account always has $100 for each period, if the base period is 7, the MTD amount is $50 (50% of $100), and the YTD is $230 (6 periods of $30 plus 1 period of $50). The report will only have a single MTD colu...
How can I make Grouping with summary rows ABOVE detail the default
In Excel 2002 on the Menu: Data > Group and Outline > Settings I'd like to make the setting "Summary rows below detail" unchecked as the default. I have never wanted it with the summary row below the detail therefore I have to go into this EVERYTIME I use grouping. Is there a way I can make it unchecked as the default? > Is there a way I can make it > unchecked as the default? Can't say. An option would be to use code to run it (stored in personal.xls & assigned to a custom toolbar button) ; Application.Dialogs(xlDialogSubtotalCreate).Show , arg6:=Fal...
how to count email bytes
Hi NG, I want to measure the amount of emails flowing to our system in one day. With the ESM, I am able to view emails depending on date and the ESM display the size of every email. Is there a possibility to copy the query result in a text file? Or is there another way to get the quantity of all emails per day? Thanks in advance, Birte Have a look at some of the Perfmon counters for SMTP (Messages Received total, Messages Sent total, etc). They ought to be able to get you the rough statistics you are looking for. The bad news here is that I believe in order to reset the perfmon counte...
Lookup and count?
I can't figure out how to do something. I have a list of names on one sheet and I want to lookup another sheet, compare each name with a list of names and when a match is found, check the number of tasks that person has done (which is stored in an adjacent cell). I don't know how to do this. Any ideas? Cheers. Bobby Got it. SUMIF. "Bobby" <bobby@aventuremail.com> wrote in message news:3g1ggiFa71rlU1@individual.net... >I can't figure out how to do something. > > I have a list of names on one sheet and I want to lookup another sheet, > compare...
Summing rows
I've got Excel 97. It's easy to arrange summation of data in a column of cells. You put in a result cell the summation formula for every column you want to sum. You can use the same formula for summation in rows, but do you have laboriously enter, one cell at a time, the regular summation formula. If I had 200 rows, I'd have to enter the required formula 200 times. Cannot I just highlight the column which contains the summations and do something, so that each cell in the column will end up containing the row summation? TIA? Hi Richard try something like the following in colu...
Default Row Height in Excel 2003
This may have been covered before - and apologies if so - but I can't change the default row height from 12.75 to 15.00 each time I open excel 2003. I have tried saving as book1.xlt , book.xlt and even renaming the original book1.xlt to bookold.xlt but still when I open up excel the default row height remains 12.75 Would really appreciate if someone could either let me know how to do this or if version 2003 won't let you! I know I did it in version 97 derek Where are you saving BOOK.XLT? It must be saved into your XLSTART folder, usually at C:\Documents and Settings\username...
Copy & paste row in anther worksheet
how to copy & paste and Cut & paste data row from one worksheet or another one worksheet?? The requirement is I need to create a new worksheet first, and the copy & paste or cut & paste a data row from one exist worksheet to the newly created worksheet. because i am a novice in writing VBA in Excel , pls help The easiest way to learn this kind of stuff is to record a macro when you do it manually. In fact, record two while you try both options. I like to add the worksheet first, then do the cut/copy, then paste. Sometimes, there are things that make xl lose the the cut/...
How to count the number of text?
There is a list of text from cell A1 to A100, I would like to count the number of "Mary" within this range, each cell may contain a senstance, such as Mary goes to school by bus, or John meets Mary in Library ... etc. So far, the counter for Mary is 2. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric Try =COUNTIF(A1:A100,"*Mary*") OR with the query string in cell B1 =COUNTIF(A1:A100,"*" & B1 & "*") -- Jacob (MVP - Excel) "Eric" wrote: > There is a list of text ...
hiding a row in a spreadsheet if calculated value = 0
Hello, I am working on invoices for my employer. Each row in the spreadsheet has a calculated value. When the calculated value in the row = 0, then I am to 'hide' the row. If the calculated value is not =0, (could be positive or negative), then I am to display or 'unhide' the row. Does anyone know how to do this? Macros and VBA responses are ok. :) -- Thank you, Electricbluelady Electricbluelady, Select the cells with the values (including the header row), then use Data / Filters.... AutoFilter. Select the dropdown on the header cell, then choose Custom, Does Not Equa...
How do I show two rows of worksheet tabs instead of one?
I have about 30 worksheets in an Excel Workbook. How can I show more than one row of worksheet tabs on the screen at once without scrolling? Not possible Rob But maybe you like the option to right click on the arrows on the left of the first sheet tab -- Regards Ron de Bruin http://www.rondebruin.nl "RobtJWitt" <RobtJWitt@discussions.microsoft.com> wrote in message news:046F7DAD-E770-4961-8BF9-A3211950A4B8@microsoft.com... >I have about 30 worksheets in an Excel Workbook. How can I show more than > one row of worksheet tabs on the screen at once without scrolling...
Go to the last-new row in a datasheet view
What is the code to go to the last row where is added a new record when you type something? I tryed adding a record to the form recordset but I don't want to leave that record empty, I just want it to be ready to load data into. Greetings from Paraguay. Claudio Bogado Pompa. Use the GotoRecord method. It is explained in VBA Help. If you are using a macro, then it is the GotoRecord action, also in VBA Help. -- Dave Hargis, Microsoft Access MVP "Claudio Bogado Pompa" wrote: > What is the code to go to the last row where is added a new record > when you type something...
Too many rows displaying on spreadsheet
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have a spreadsheet that has roughly 1,000 rows by 15 columns. HOWEVER, rather than the spreadsheet displaying a few more hundred below that, it is displaying 10,000's more rows. It is a bother rather than a real problem, but i can't use the scroll bar on the right. Any way to reduce the number of empty rows that are displaying? Thanks The number of rows is fixed @ 1,048,576 but you can select & hide the ones you don't want to have displayed using the Format> Row> Hide command. The unanswered quest...
Count Text Values
I have a column which contains text values whichare either yes /no. How can I get a totals column at the bottom whichcounts the number of "Yes" values. I'm fine if the cell contents are actual numbers but how do I do it if they are text? -- Regards Andy Andy Roberts Win XP, Office 2007 =COUNTIF(A1:10,"yes") =COUNTIF(A1:10,"no") -- Regards Dave Hawley www.ozgrid.com "Andy Roberts" <andy@blue-bean.co.uk> wrote in message news:ukJ0HJJ1KHA.224@TK2MSFTNGP06.phx.gbl... >I have a column which contains text...
VB Code for Hiding Rows
This code will hide row thirty if cell E30="". What should i do so as the line will be hidden if E30="" and F30="" Me.Rows(30).Hidden = CBool(Me.Range("E30").Value = "") Khalil Handal if range("e30")="" and range("f30")="" then rows(30).hidden =true or with range("e30") if .value="" and .offset(,1)="" then end with -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Khalil Handal" <handalk@stthom.edu> wro...
Counting entries from the right
For a while now I have been using some code to determine how far down a worksheet the last line of data appears. For example: With ActiveSheet lngLastRow = .Range("A65536").End(xlUp).Row End With Does anyone know the equivalent function to count how far out from the left (i.e. if there is data in columns A to G) - I'm sure it must exist but cannot quite figure it out. thanks check out the specialcells method - XLLastCell would POSSIBLY give you the data you want? "Graham Whitehead" <gwhitehead77@hotmail.com> wrote in message news:u55DHCLiGHA.489...
count query and negative numbers question
Hi All, Pretty new to database queries so apologise if I sound dumb! I have a database for stock control of energy efficient light bulbs for a charity that gives the bulbs out free to folks in need. The database has various fields including type of bulb, location, quantity and whether the stock has been added to stock or removed. I want to know how many of the different types of bulbs are in stock at any time. I have tried to run a query which makes the 'quantity' amount of bulbs removed to be negative (puts a '-' in front of number). I then want to run a second q...
Insert row VBA code
Thank you for your help. I am trying to build and find code like below that will insert a row in columns A to P. If I put my curser on A5 and click on a macro button then it will insert a line on A6 from column A to P. Or if I put the curser on A10 or A50 that it would do the same thing. I have tried the code below and I know it is wrong for the "Entire Row" is wrong. But I cannot make it work. Thank you for your help. Sub InsertRows() Sheets("sheet1").Activate Selection.EntireRow.Insert Shift:=xlDown Sub yInsertA3P3CellsDown() Range("A3:P3").Insert Shift...
Displaying a count as a percentage within subtotals in a pivot tab
Excel 2007 I have a count field in a pivot table column that I want to display as a percentage. If I have no subtotals, the option "% of column" in Value Field settings works great. However if I add subtotals to the column and want the percentages to calculate WITHIN SUBTOTALS as opposed to the whole column, I can't find an option in the Value Field settings that does that (I tried "% Of" to no avail). Is this even doable? -- John Shahan Excel 2007 Pivot Table Subtotals with % of column Use helper column or macro: http://www.mediafire.com/file/iz4wwuihm1b/...
adding in a row
Is there a way to add the last three none blank cells in a row. I want to have 12 cells in a row some will have numbers and some wil not. Just want the last three added as I add more data in the column from left to right ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com > Is there a way to add the last three none blank cells in a row. I typed this pretty much verbatim into Google & found this from Peo - =SUM(IV1:INDEX(1:1,LARGE(COLUMN(1:1)*(1:1<>"")...
Row Formula
How do I create a formula that returns the furthest to the right last non zero number in a row with up to 15 columns ie colums shown with values in cells A1 to L1 A B C D E F G H I J K L 1 4 6 0 8 4 0 7 5 How Do I create a formula to return the value of 5, in this case cell I1? Hi! Here's one way. Entered with the key combo of CTRL,SHIFT,ENTER: =INDEX(A1:L1,MAX(IF(A1:L1>0,COLUMN(A1:L1)))) Biff <tsanders123@hotmail.com> wrote in message news:1114750851.241579.299070@f14g2000cwb.googlegroups.com... > How do I create a formula that returns the furthest to the right las...
Delete Empty Rows
Is there a simple macro that will enable me to delete a row if there is no information in it. I have a very large spread sheet but a number of the rows are empty and it would take a considerable amount of time to delete manually. kuri If there is always a blank in a certain column, you could sort on that column to leave all of the blank rows together. Andy. "kuri" <anonymous@discussions.microsoft.com> wrote in message news:083a01c3a44d$58182400$a601280a@phx.gbl... > Is there a simple macro that will enable me to delete a > row if there is no information in it. I h...
SORT Header Row(s)
My worksheet column headers are in 2 rows. Short of combining those 2 rows of header text into 1 row, is there a way designate that there are 2 header rows when sorting using the sort function, below? DATE | SORT | "My data range has header row" TIA, ~Karen No. Maybe you can just ignore the top header row and include just the bottom header and choose "my range has headers"... Or maybe you could just ignore both headers and not choose that option. ps. I'd create a single header row. You can use alt-enter to force a new line in the cell. Karen wrote: > >...
COUNT # times text value occurrs in range
I want to count the number of times the values 'TRUE' & 'FALSE' occurr in a list I've used the following formula but it's returned the wrong value: =COUNTA(TRUE,or,FALSE,G7:G37) That returned the value: 34 but there are only 31 records in the lis which are TRUE or FALSE -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=31871 Hi Loscherland use the Countif function...
can some one explain to me the count(if()) function??
i got this array formula... where e4:e86, aq6 and aq5 are dates ={COUNT(IF(($E$4:$E$86>=AQ6)*($E$4:$E$86<=AQ5),$E$4:$E$86))} i understand it... but how does it differ to say a "countif" function? can the above function be simplified -- Message posted from http://www.ExcelForum.com The COUNTIF function can handle only one condition while yours has two. =COUNTIF(E4:E86,">="&AQ6) -- Jim Rech Excel MVP "sokevin >" <<sokevin.17x8wh@excelforum-nospam.com> wrote in message news:sokevin.17x8wh@excelforum-nospam.com... |i got this ar...
How do I count based on two conditions?
Hi there. I have a long lists of data to sort though and I am trying to write a formula that counts that number of entries for a company with a certain condition entered in another column. For instance Company name Condition Big Corp operatonal Small corp operational Big Corp non-operational How do I count where Big Corp is operational and where it is non-operational? Thanks, Mark Try this, Mark: http://www.officearticles.com/excel/count_using_multiple_criteria_in_microsoft_excel.htm ************ Hope it helps! Anne Troy www.OfficeArticles.com "...
I'm trying to construct an FRx report that calculates a fixed percentage of a GL Acct, where the percentage changes depending on the base period. For example, let's say I want the percentage to be 30% for the first 6 months, and 50% for the last 6 months. How can I get the MTD amount for the base period, as well as the YTD amount, to be correct? If we assume that an expense account always has $100 for each period, if the base period is 7, the MTD amount is $50 (50% of $100), and the YTD is $230 (6 periods of $30 plus 1 period of $50). The report will only have a single MTD colu...
How can I make Grouping with summary rows ABOVE detail the default
In Excel 2002 on the Menu: Data > Group and Outline > Settings I'd like to make the setting "Summary rows below detail" unchecked as the default. I have never wanted it with the summary row below the detail therefore I have to go into this EVERYTIME I use grouping. Is there a way I can make it unchecked as the default? > Is there a way I can make it > unchecked as the default? Can't say. An option would be to use code to run it (stored in personal.xls & assigned to a custom toolbar button) ; Application.Dialogs(xlDialogSubtotalCreate).Show , arg6:=Fal...
how to count email bytes
Hi NG, I want to measure the amount of emails flowing to our system in one day. With the ESM, I am able to view emails depending on date and the ESM display the size of every email. Is there a possibility to copy the query result in a text file? Or is there another way to get the quantity of all emails per day? Thanks in advance, Birte Have a look at some of the Perfmon counters for SMTP (Messages Received total, Messages Sent total, etc). They ought to be able to get you the rough statistics you are looking for. The bad news here is that I believe in order to reset the perfmon counte...
Lookup and count?
I can't figure out how to do something. I have a list of names on one sheet and I want to lookup another sheet, compare each name with a list of names and when a match is found, check the number of tasks that person has done (which is stored in an adjacent cell). I don't know how to do this. Any ideas? Cheers. Bobby Got it. SUMIF. "Bobby" <bobby@aventuremail.com> wrote in message news:3g1ggiFa71rlU1@individual.net... >I can't figure out how to do something. > > I have a list of names on one sheet and I want to lookup another sheet, > compare...
Summing rows
I've got Excel 97. It's easy to arrange summation of data in a column of cells. You put in a result cell the summation formula for every column you want to sum. You can use the same formula for summation in rows, but do you have laboriously enter, one cell at a time, the regular summation formula. If I had 200 rows, I'd have to enter the required formula 200 times. Cannot I just highlight the column which contains the summations and do something, so that each cell in the column will end up containing the row summation? TIA? Hi Richard try something like the following in colu...
Default Row Height in Excel 2003
This may have been covered before - and apologies if so - but I can't change the default row height from 12.75 to 15.00 each time I open excel 2003. I have tried saving as book1.xlt , book.xlt and even renaming the original book1.xlt to bookold.xlt but still when I open up excel the default row height remains 12.75 Would really appreciate if someone could either let me know how to do this or if version 2003 won't let you! I know I did it in version 97 derek Where are you saving BOOK.XLT? It must be saved into your XLSTART folder, usually at C:\Documents and Settings\username...
Copy & paste row in anther worksheet
how to copy & paste and Cut & paste data row from one worksheet or another one worksheet?? The requirement is I need to create a new worksheet first, and the copy & paste or cut & paste a data row from one exist worksheet to the newly created worksheet. because i am a novice in writing VBA in Excel , pls help The easiest way to learn this kind of stuff is to record a macro when you do it manually. In fact, record two while you try both options. I like to add the worksheet first, then do the cut/copy, then paste. Sometimes, there are things that make xl lose the the cut/...
How to count the number of text?
There is a list of text from cell A1 to A100, I would like to count the number of "Mary" within this range, each cell may contain a senstance, such as Mary goes to school by bus, or John meets Mary in Library ... etc. So far, the counter for Mary is 2. Does anyone have any suggestions on how to do it? Thanks in advance for any suggestions Eric Try =COUNTIF(A1:A100,"*Mary*") OR with the query string in cell B1 =COUNTIF(A1:A100,"*" & B1 & "*") -- Jacob (MVP - Excel) "Eric" wrote: > There is a list of text ...
hiding a row in a spreadsheet if calculated value = 0
Hello, I am working on invoices for my employer. Each row in the spreadsheet has a calculated value. When the calculated value in the row = 0, then I am to 'hide' the row. If the calculated value is not =0, (could be positive or negative), then I am to display or 'unhide' the row. Does anyone know how to do this? Macros and VBA responses are ok. :) -- Thank you, Electricbluelady Electricbluelady, Select the cells with the values (including the header row), then use Data / Filters.... AutoFilter. Select the dropdown on the header cell, then choose Custom, Does Not Equa...
How do I show two rows of worksheet tabs instead of one?
I have about 30 worksheets in an Excel Workbook. How can I show more than one row of worksheet tabs on the screen at once without scrolling? Not possible Rob But maybe you like the option to right click on the arrows on the left of the first sheet tab -- Regards Ron de Bruin http://www.rondebruin.nl "RobtJWitt" <RobtJWitt@discussions.microsoft.com> wrote in message news:046F7DAD-E770-4961-8BF9-A3211950A4B8@microsoft.com... >I have about 30 worksheets in an Excel Workbook. How can I show more than > one row of worksheet tabs on the screen at once without scrolling...
Go to the last-new row in a datasheet view
What is the code to go to the last row where is added a new record when you type something? I tryed adding a record to the form recordset but I don't want to leave that record empty, I just want it to be ready to load data into. Greetings from Paraguay. Claudio Bogado Pompa. Use the GotoRecord method. It is explained in VBA Help. If you are using a macro, then it is the GotoRecord action, also in VBA Help. -- Dave Hargis, Microsoft Access MVP "Claudio Bogado Pompa" wrote: > What is the code to go to the last row where is added a new record > when you type something...
Too many rows displaying on spreadsheet
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have a spreadsheet that has roughly 1,000 rows by 15 columns. HOWEVER, rather than the spreadsheet displaying a few more hundred below that, it is displaying 10,000's more rows. It is a bother rather than a real problem, but i can't use the scroll bar on the right. Any way to reduce the number of empty rows that are displaying? Thanks The number of rows is fixed @ 1,048,576 but you can select & hide the ones you don't want to have displayed using the Format> Row> Hide command. The unanswered quest...
Count Text Values
I have a column which contains text values whichare either yes /no. How can I get a totals column at the bottom whichcounts the number of "Yes" values. I'm fine if the cell contents are actual numbers but how do I do it if they are text? -- Regards Andy Andy Roberts Win XP, Office 2007 =COUNTIF(A1:10,"yes") =COUNTIF(A1:10,"no") -- Regards Dave Hawley www.ozgrid.com "Andy Roberts" <andy@blue-bean.co.uk> wrote in message news:ukJ0HJJ1KHA.224@TK2MSFTNGP06.phx.gbl... >I have a column which contains text...
VB Code for Hiding Rows
This code will hide row thirty if cell E30="". What should i do so as the line will be hidden if E30="" and F30="" Me.Rows(30).Hidden = CBool(Me.Range("E30").Value = "") Khalil Handal if range("e30")="" and range("f30")="" then rows(30).hidden =true or with range("e30") if .value="" and .offset(,1)="" then end with -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Khalil Handal" <handalk@stthom.edu> wro...
Counting entries from the right
For a while now I have been using some code to determine how far down a worksheet the last line of data appears. For example: With ActiveSheet lngLastRow = .Range("A65536").End(xlUp).Row End With Does anyone know the equivalent function to count how far out from the left (i.e. if there is data in columns A to G) - I'm sure it must exist but cannot quite figure it out. thanks check out the specialcells method - XLLastCell would POSSIBLY give you the data you want? "Graham Whitehead" <gwhitehead77@hotmail.com> wrote in message news:u55DHCLiGHA.489...
count query and negative numbers question
Hi All, Pretty new to database queries so apologise if I sound dumb! I have a database for stock control of energy efficient light bulbs for a charity that gives the bulbs out free to folks in need. The database has various fields including type of bulb, location, quantity and whether the stock has been added to stock or removed. I want to know how many of the different types of bulbs are in stock at any time. I have tried to run a query which makes the 'quantity' amount of bulbs removed to be negative (puts a '-' in front of number). I then want to run a second q...
Insert row VBA code
Thank you for your help. I am trying to build and find code like below that will insert a row in columns A to P. If I put my curser on A5 and click on a macro button then it will insert a line on A6 from column A to P. Or if I put the curser on A10 or A50 that it would do the same thing. I have tried the code below and I know it is wrong for the "Entire Row" is wrong. But I cannot make it work. Thank you for your help. Sub InsertRows() Sheets("sheet1").Activate Selection.EntireRow.Insert Shift:=xlDown Sub yInsertA3P3CellsDown() Range("A3:P3").Insert Shift...
Displaying a count as a percentage within subtotals in a pivot tab
Excel 2007 I have a count field in a pivot table column that I want to display as a percentage. If I have no subtotals, the option "% of column" in Value Field settings works great. However if I add subtotals to the column and want the percentages to calculate WITHIN SUBTOTALS as opposed to the whole column, I can't find an option in the Value Field settings that does that (I tried "% Of" to no avail). Is this even doable? -- John Shahan Excel 2007 Pivot Table Subtotals with % of column Use helper column or macro: http://www.mediafire.com/file/iz4wwuihm1b/...
adding in a row
Is there a way to add the last three none blank cells in a row. I want to have 12 cells in a row some will have numbers and some wil not. Just want the last three added as I add more data in the column from left to right ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com > Is there a way to add the last three none blank cells in a row. I typed this pretty much verbatim into Google & found this from Peo - =SUM(IV1:INDEX(1:1,LARGE(COLUMN(1:1)*(1:1<>"")...
Row Formula
How do I create a formula that returns the furthest to the right last non zero number in a row with up to 15 columns ie colums shown with values in cells A1 to L1 A B C D E F G H I J K L 1 4 6 0 8 4 0 7 5 How Do I create a formula to return the value of 5, in this case cell I1? Hi! Here's one way. Entered with the key combo of CTRL,SHIFT,ENTER: =INDEX(A1:L1,MAX(IF(A1:L1>0,COLUMN(A1:L1)))) Biff <tsanders123@hotmail.com> wrote in message news:1114750851.241579.299070@f14g2000cwb.googlegroups.com... > How do I create a formula that returns the furthest to the right las...
Delete Empty Rows
Is there a simple macro that will enable me to delete a row if there is no information in it. I have a very large spread sheet but a number of the rows are empty and it would take a considerable amount of time to delete manually. kuri If there is always a blank in a certain column, you could sort on that column to leave all of the blank rows together. Andy. "kuri" <anonymous@discussions.microsoft.com> wrote in message news:083a01c3a44d$58182400$a601280a@phx.gbl... > Is there a simple macro that will enable me to delete a > row if there is no information in it. I h...
SORT Header Row(s)
My worksheet column headers are in 2 rows. Short of combining those 2 rows of header text into 1 row, is there a way designate that there are 2 header rows when sorting using the sort function, below? DATE | SORT | "My data range has header row" TIA, ~Karen No. Maybe you can just ignore the top header row and include just the bottom header and choose "my range has headers"... Or maybe you could just ignore both headers and not choose that option. ps. I'd create a single header row. You can use alt-enter to force a new line in the cell. Karen wrote: > >...
COUNT # times text value occurrs in range
I want to count the number of times the values 'TRUE' & 'FALSE' occurr in a list I've used the following formula but it's returned the wrong value: =COUNTA(TRUE,or,FALSE,G7:G37) That returned the value: 34 but there are only 31 records in the lis which are TRUE or FALSE -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=31871 Hi Loscherland use the Countif function...
can some one explain to me the count(if()) function??
i got this array formula... where e4:e86, aq6 and aq5 are dates ={COUNT(IF(($E$4:$E$86>=AQ6)*($E$4:$E$86<=AQ5),$E$4:$E$86))} i understand it... but how does it differ to say a "countif" function? can the above function be simplified -- Message posted from http://www.ExcelForum.com The COUNTIF function can handle only one condition while yours has two. =COUNTIF(E4:E86,">="&AQ6) -- Jim Rech Excel MVP "sokevin >" <<sokevin.17x8wh@excelforum-nospam.com> wrote in message news:sokevin.17x8wh@excelforum-nospam.com... |i got this ar...
How do I count based on two conditions?
Hi there. I have a long lists of data to sort though and I am trying to write a formula that counts that number of entries for a company with a certain condition entered in another column. For instance Company name Condition Big Corp operatonal Small corp operational Big Corp non-operational How do I count where Big Corp is operational and where it is non-operational? Thanks, Mark Try this, Mark: http://www.officearticles.com/excel/count_using_multiple_criteria_in_microsoft_excel.htm ************ Hope it helps! Anne Troy www.OfficeArticles.com "...