how do I sum only visible data in a column

I have some rows manually hidden, Please is there a formula I can apply to 
return the sum of the unhidden data contain in a column?
0
Afolabi (4)
7/26/2005 5:49:02 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
570 Views

Similar Articles

[PageSpeed] 24

Hi Afolabi,

If you don't want to use VBA (create a UDF), you could review the following 
pages:
http://www.jkp-ads.com/Articles/ExcelNames08.htm
http://www.jkp-ads.com/Articles/ExcelNames09.htm

In any case, there is a fundamental issue with both approaches: since 
hiding/unhiding rows doesn't trigger any event, such event will have to be 
forced from time to time, or maybe you can leave with the formulae updating 
their result in the next recalculation of the sheet.

Regards,
KL


"Afolabi" <Afolabi@discussions.microsoft.com> wrote in message 
news:9EFC5E76-AF78-4657-8AD1-395B39451002@microsoft.com...
>I have some rows manually hidden, Please is there a formula I can apply to
> return the sum of the unhidden data contain in a column? 


0
7/26/2005 7:13:26 AM
Depending upon your needs, there is a Copy of visible only cells, click
Edit, Go To, select Visible Cells, then select the range and paste. 
This new range can then be totalled.

Otherwise . . I think it's a little VBA code


-- 
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=390100

0
7/26/2005 7:13:29 AM
If you're using xl2003, you can use =subtotal().  This worksheet function has
been enhanced to include options to ignore manually hidden rows.

=subtotal(109,a2:a22)

for instance.

Afolabi wrote:
> 
> I have some rows manually hidden, Please is there a formula I can apply to
> return the sum of the unhidden data contain in a column?

-- 

Dave Peterson
0
petersod (12005)
7/26/2005 12:15:42 PM
Reply:

Similar Artilces:

Changing Rows to Columns
Is it possible to easily rotate a spreadsheet or part of a spreadshet, to change the data in rows to columns? Do to a sorting problem in the charts I create, it seems I have to sort the data in the spreadsheet first. Any help would be appreciated. Hi, Sure is a way. select & copy your data. then select the cell where you want to re-paste; right-click and select paste special; in the dialog box check "transpose". Ok. jeff >-----Original Message----- >Is it possible to easily rotate a spreadsheet or part of a >spreadshet, to change the data in rows to columns? D...

Charting flood data
Does anyone know how to get excel to chart data on a chart like Extreme log paper or log Gumbel chart? Never heard of Extreme log paper or a Gumbel chart, but based on the definition of the Gumbel distribution at Mathworld http://mathworld.wolfram.com/GumbelDistribution.html I presume that it plots ln(-ln(y)) vs. x That is not a chart option in Excel, but you can calculate ln(-ln(y)) in a separate column and plot it directly. Jerry Rick wrote: > Does anyone know how to get excel to chart data on a chart like Extreme log paper or log Gumbel chart? You can use a technique like t...

Layout/Formatting problems of Data Queries in Excel?
I am using Excel 2003 to display a simple table result from an Access 2003 table. 1. When I select a cell location for a Data Query in the XL spreadsheet, Excel moves all text, above or below that cell, to adjacent columns to the right of the selected cell. (Also, there is no undo option and it all has to be manually restored!) 2. In 'Data Range Properties', the option: 'Insert entire rows for new data, clear unused cellls' is selected. Despite this, the data is displayed with only cells inserted, not entire rows, rendering my report useless. ...

Read Only Data
I am trying to "move" a data file from one Win 98 machine to another via a CD Disk. The data file was created in Access 2000 and the receiving machine is also using Access 2000. I can copy the file into a folder in the new machine but when trying to open it I get a message that it is read only and cannot be modified. How can I get a workable copy into the new machine? Tom D Select the file in Windows Explorer, right-click, choose Properties and uncheck the Read-Only check box. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSte...

Correct Currency Data Type and Validation Coding
Any suggestions are so greatly appreciated as I am really really stuck Using Access 2003 on XP OS My Access and VBA experience is limited. I have a several fields that I am checking for validation as currency. I have two questions 1) what is the best data type for currency. Currently I am using Table Data Type = Number. (I have tried other types with no luck) Field Size = Single Format=Currency. On the form I am formating to currency. 2) I want to be able to check for whole numbers and for those that are entered with a decimal. Currently I am using the below. Pri...

Adding Data to a Non-Contiguous Range in Excel 2007
I have a chart based upon a non-contiguous range. When I click on the chart and "Select Data", and then click on the Edit button, I get the expected dialog box with Selected Values. However, I can't manually edit the values displayed, even after pressing F2. I click on the icon to take me to the spreadsheet, and it displays the non-contiguous values in dashed boxes. I hold down Ctrl and click the next box, and it de-selects everything. Then I try to add them all again, and it won't let me add the last range. Is there a limit in 2007 for how many non-contiguous va...

Need to pad with Zeros to the existing list of numbers in a column
I have some values in a spreadsheet and I would like to pad with zeros to the right side. For Ex. 1224 should be converted as 01234 Format the number as Category: Custom and Type: 00000 It won't change the number, just the way it is displayed Regards Trevor "dwaraka" <dwaraka@discussions.microsoft.com> wrote in message news:B2E73E51-3213-47EB-9809-D4AF2088A7F1@microsoft.com... >I have some values in a spreadsheet and I would like to pad with zeros to >the > right side. For Ex. 1224 should be converted as 01234 ...

Sample Access Database using Oracle Data and needing Time/Date Sta
Does anyone know of a good web site out there that has some sample Access Databases that invoke and use Oracle Data or even SQL Server for that matter that also uses a Time/Date Stamp driven Map to drive and filter a query to get results back via an ADO call??? I have been asked to create an Access Database as a GUI Type application which will utilize an Access Form and require User Input to Enter a Date and Time which will ultimately filter the data. I have no idea where to begin on something like this. (Boy....that sounds like a tall order....) Any help would be GREATLY...

Presenting Pivot table data as product of 2 source data columns
I have the following data F1 F2 D1 D2 F01 1 10 1 F02 2 20 3 F01 2 30 6 F01 5 40 4 F02 6 40 5 F1 to be presented in rows F2 to be presented in columns D1* D2 to be presented as data Is that possible without defining an extra column in my source data? regards, Louis Louise - Yes, it's a calculated pivottable field. Once you have the pivottable established except for the data values, select Options tab, Formulas drop down arrow, calculated field. "loconel" wrote: > > I have the following data > > F1 F2 D1 D2 > F01 1 10 1 > F02 2 20 3 > F01 2 30 6 >...

Line-Column on 2 Axes
Using Excel 2003. I have three columns of data I'd like to chart. Two of the columns (Orders and Margins) are dollar amounts that I would like to chart as two separate lines on a y-axis value of dollar amounts. The third column is Number of Orders that I would like to chart as a column on a y-axis value of numbers. I selected the custom type chart from the wizard named Line-Column on 2 Axes. The description indicates that "columns are plotted on the primary axis and lines are plotted on the secondary axis." My problem is that the chart will only display one set of c...

How to remove a column in a list on a SharePoint site?
I can change the display property of a column by Site action->site settings->edit view I can uncheck them from the display. But when adding a new item, they still display. How do I change that? Specifically I created a task list in the site which comes with a column named Category and I want to remove it. "GailH" wrote: > I can change the display property of a column by Site action->site > settings->edit view I can uncheck them from the display. But when adding a > new item, they still display. How do I change that? Go into the settings for th...

How to sort one column while keeping others row data in sync?
I'm trying to delete data I don't need but I need to keep all Row data in sync while I sort any of the column data. What would I need to do to get this done? Thanx Hi rather than selecting a column & sorting it, click on one cell, then choose data / sort from the menu ... choose your column and sort. Cheers JulieD "newbie" <nospam@spamless.com> wrote in message news:%23TV4K9BVEHA.2520@TK2MSFTNGP12.phx.gbl... > I'm trying to delete data I don't need but I need to keep all Row data in > sync while I sort any of the column data. > What would I ne...

Autosorting and working out medalists for data.
"Paul" <none> wrote in message news:<#dBs3nAwDHA.1596@TK2MSFTNGP10.phx.gbl>... > "Steve Hill" <steve_hill4nojunk@hotmail.com> wrote in message > news:1a613ac7.0312110856.4ee75392@posting.google.com... > > I am trying to find a way in which I have a block of data containing > > mostly letters between 1 and 10, but also the occasional "X" and > > return the sum of the block counting the X's as 10 as well as the > > orignal 10's. This would be for an archery spreadsheet and would then > > be able to add up...

bringing in data from another worksgroup to a current workgroup
I have 2 worksheets in different workgroups. They contain similar data but they columns are not in the same order and i don't need all of the columns. Cutting and pasting is tedious because of the inconsistency in the layout. Is there anyway to instruct worksheet A to capture the data from a range of rows in a particular column in worksheet b? In other words, i have the following columns WORKSHEET 1 FIRST NAME: LAST NAME: ID NUMBER, MISC a : MISC B WORKSHEET 2 LAST NAME: FIRST NAME: MISC c: ID NUMBER: MISC D; MISC B I would like to bring the similar fields in WORKSHEET 2, t...

How do I input address data into a table in Excel 2007?
I want to use a range of cells in one sheet to input address infomation and link it to another sheet table. What logical functions or formulas do I use so that the input will collect in the table in the next avaible row? Thanks ...

Data lost on Exchange 5.5 to 2003 migration
After performing a migration from Exchange 5.5 to 2003, All seemed to go pretty smoothly. There was one exception. A user complained that her mail and everything was not there. I look at the PST file that EXMERGE created from the 5.5 server and it's all of 30k. Check on the old server again, yep...35k. Told her that was all, but come to find out, she had tons of stuff in there. I was able to figure out from a 3 month old backup that she did indeed have lots of stuff in there. I used EXMERGE on that one and put that info into her new mailbox. Problem I have is with the current datab...

Discontinuous lines in series due to gaps in data points
Sorry, the title probably sounds a little confusing, but hopefully my problem is a quick fix. I have some charts, and in them about 6 different series. Along the x axis, is a bunch of different values, and each series has some values corresponding to some of the x axis values, but not all. So basically right now, some series have gaps, and rather than connect the gaps with a line, Excel seems to just leave a gap in the series, and continue again at the next data point. Hopefully you understand what I'm saying. Is there an easy way to just fill in these series gaps with a straight...

Statistics function to reprofile/redistribute a data set over diff time period
We have a series of data over a time interval. We want to maintain the profile over a reduced interval. We want to total value of the data series to remain the same, but have a new profile with a similar distribution. Simple Example. A data set over 6 (x) month period like this 1 2 2 2 2 1 becomes the following over a 3 (y) month period. 3 4 3 Our problem set has various values of x and y and data set is distributed randomly. Thanks, Vilok I am sure what you are asking for makes sense to you, but I don't know what you mean by 'maintain the profile.' In the original data...

format cells that will show the data in parenthesis
I would like to format some cells that I put data into to be in ( ) Parentesis.. How do I do this? An example would have been very useful. Assuming it's negative numbers you want to parenthesize, use a custom format of: #,##0.00;(#,##0.00) Regards, Fred "Hoop" <Hoop@discussions.microsoft.com> wrote in message news:83F809EA-4095-421E-A77C-F4E8DAD85005@microsoft.com... >I would like to format some cells that I put data into to be in ( ) > Parentesis.. How do I do this? ...

data level security in GP
Hello everyone, how can we apply data level security per user, i.e a certain can only be allowed to see a certain customres, vendors, items etc etc. i know that GL accounts can be restricted per user using the organizational structure but how to secure other data ? any help will be really helpful. Regards Ashfaq Look at Field Level Security and Field Level Scripting, both from Great Plains. "Ashfaq Ali" <ashfaq@cyberdude.com> wrote in message news:ej3C9ucFFHA.3972@TK2MSFTNGP15.phx.gbl... > Hello everyone, > > how can we apply data level security per user, i.e a...

How do I make a table that will truncate my data field?
I have a field "0002222 Catheter". I need to create a table that just pulls out the 2222. I thought I could truncate somehow? If the numbers are always first, create a query, and type this into the field row: Val(Nz([F1], "0")) After verifying that this give the right results, you can turn it into a Make Table query, along with the other fields you want. Make Table is on the Query menu, in query design view. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne ...

drop down list that changes the data of 3 other cells
I select a customer from a drop down list and on selection I need 3 discounts to be input into the three cells below. These vary from customer to customer any ideas how I can do This? It sounds like an issue where VLOOKUP can be used (to see how to use VLOOKUP go to http://www.auditexcel.co.za/lookupandreference.html ). Have 3 vlookup cells that make reference to the drop down customer name and pull through the relevant discounts. If I have misunderstood please rephrase your question. "Mark R Burgess" wrote: > I select a customer from a drop down list and on selection I n...

Row & Column Display
I need to fix the columns to show letters on my spreadsheet. It looks like this: 1 2 3 4 5 etc 2 3 4 5 Help! ...

import data from .txt to excel
Hi, I have various report extracted from different applications in .txt format. I would like to transfer the same report from .txt format to excel.......the report in .txt is generated on regular basis. The report in .txt format contains many unwanted fileds. Can we write a macro to export the data from .txt to excel so that the fileds required can only be exported to excel. Please help.... Cheers, Mandeep The simplest way is to record the process of opening a .txt file with the wizard as a macro. You will have an option for each column to omit it. Regards, Stefi „Mandeep Dhami”...

sum between to dates from a pivot table...
I have this huge file with employment data. I want to make sure i have enough employees at the time when we have lots of customers, now when the vacations coming up. I have made a pivot table of the names and the work times of the employees every day, to the right of it i made a graphical presentation of their work times ciontaining formulas and custom formatting so i easily see where i need more people, i don't always have the same number of employees at work. I would like for my model to sum up the employees to though (as shown red text in the yellow field in the pic). The dates are fixe...