Help: AutoFilter not 'hiding' data from other formulas

I'm using AutoFilter on a columns of personal data for which I've set
up other calculations (averages, percentiles, etc). I was hoping that
if I filtered, say, just for women, then the other calcualtions would
reflect only the women's data, but they still seem to reflect the whole
column of data, including data that's hidden by AutoFilter. Is there
any easy way to fix this? Thanks!

Andrew

0
agbiggs (32)
4/13/2006 3:18:44 PM
excel 39879 articles. 2 followers. Follow

6 Replies
335 Views

Similar Articles

[PageSpeed] 2

Do you mean that you have some SUM( ... ) formulae which do not change
to reflect only the displayed values? If so, you should change these to
SUBTOTAL(9, ... ).

Hope this helps.

Pete

0
pashurst (2576)
4/13/2006 4:13:05 PM
Check the HELP for a good explanation of the SUBTOTAL formulas which work the 
same as the regular SUM, AVERAGE, etc except they only use the visible 
rows....

hth
Vaya con Dios,
Chuck, CABGx3



"agbiggs@hotmail.com" wrote:

> I'm using AutoFilter on a columns of personal data for which I've set
> up other calculations (averages, percentiles, etc). I was hoping that
> if I filtered, say, just for women, then the other calcualtions would
> reflect only the women's data, but they still seem to reflect the whole
> column of data, including data that's hidden by AutoFilter. Is there
> any easy way to fix this? Thanks!
> 
> Andrew
> 
> 
0
CLR (807)
4/13/2006 4:35:02 PM
Andrew

I would suggest that you use the SUMIF function to create bespoke formula 
for your spreadsheet. My understanding is that autofilter only hides.

Martin

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Visit Eighty-Twenty Spreadsheet Automation for professional customised 
spreadsheet development

http://homepage.ntlworld.com/martin.rice1/

<agbiggs@hotmail.com> wrote in message 
news:1144941524.363439.15020@i39g2000cwa.googlegroups.com...
> I'm using AutoFilter on a columns of personal data for which I've set
> up other calculations (averages, percentiles, etc). I was hoping that
> if I filtered, say, just for women, then the other calcualtions would
> reflect only the women's data, but they still seem to reflect the whole
> column of data, including data that's hidden by AutoFilter. Is there
> any easy way to fix this? Thanks!
>
> Andrew
> 


0
4/13/2006 5:30:27 PM
When I looked up Help for Subtotal, it appears that you want to use 109 to 
ignore hidden values. 

"Pete_UK" wrote:

> Do you mean that you have some SUM( ... ) formulae which do not change
> to reflect only the displayed values? If so, you should change these to
> SUBTOTAL(9, ... ).
> 
> Hope this helps.
> 
> Pete
> 
> 
0
4/13/2006 5:39:02 PM
Those 1## series will make =subtotal() ignore rows hidden manually (as well as
those hidden by the filter).  

They were added in xl2003. 

Patricia Shannon wrote:
> 
> When I looked up Help for Subtotal, it appears that you want to use 109 to
> ignore hidden values.
> 
> "Pete_UK" wrote:
> 
> > Do you mean that you have some SUM( ... ) formulae which do not change
> > to reflect only the displayed values? If so, you should change these to
> > SUBTOTAL(9, ... ).
> >
> > Hope this helps.
> >
> > Pete
> >
> >

-- 

Dave Peterson
0
petersod (12004)
4/13/2006 5:48:25 PM
Thanks very much -- this points me in the right direction!

Andrew

0
agbiggs (32)
4/13/2006 6:29:51 PM
Reply:

Similar Artilces:

have to read data from Excel
Hi, I want to read and write the data from the excel file. I dont know how to do this. Any pointer will be helpful Thanks in advance Gaurav "Gaurav" <gauravgoyal121@gmail.com> ha scritto nel messaggio news:1185881881.293085.303740@d30g2000prg.googlegroups.com... > I want to read and write the data from the excel file. > I dont know how to do this. Any pointer will be helpful Hi, I think that you might consider using some file format different from XLS, e.g. you may use CSV (comma separated values) or XML. Excel can manage CSV and XML file formats, so you may exp...

help! money 2005
I last used Money 3 days ago. Today I open it up and one of the accounts is missing! There are 4 accounts at the same bank - one checking, one savings for each of 2 accounts. I open up Money this morning and my main checking account is simply gone. The savings account is there and the other account is fine. I tried "standard" repair which said it was successful, but account is still missing! Using a backup copy (not "backup") from 2 days ago and one from 3 days ago (different drive) all have file missing, which leads me to believe it's a Money problem. Any i...

need help on record changing
Hello. I have a situation in which the higher ups are restructuring territories/areas based on zip codes. Which now will require the changing the designated area of 16,000 records. There are 3 tables that are involved; tblArea - has the updated restructuring tblCustomer - has the customer info including the zip code and the tblArea primary key (AreaPK) as a foreign key tblAreaZipCode - has the listing of the zip codes for the different areas and the tblArea (AreaPK) primary key is a foreign key I need to change the AreaPK (int) in the tblCustomer table to match the one located in...

data table too long to setup
Hi All This company sells tickets to people who stays in hotels for activities they organise. I have already prepared tables to get data for sale sheet ie user will enter ticket number, Activity name,activity date,hotel name, client name ,room number,number of people. All other information will be picked up from pre-set tables.So far no problem except They sale nearly 40 different activities and their clients stay in nearly 50 hotels- if not More than that- And my pick-up table which consists of " activity name" , "hotel name ", "pick-up time", "pick- point ...

Simple Date Formula
Hello, Can some help me with the following problem - I want to make cell B3, with this date format - Jun 24, 2004 06:00:00 and add text to it either side. I've tried ="!!!"+B3+"!!!" but thi doesn't work. This calculation cell must be able to be cut and paste into a web form. Thanks, Charli -- Message posted from http://www.ExcelForum.com ="!!!" & Format(B3,"mmm dd, yyyy hh:mm:ss") & "!!!" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing di...

Refresh Imported Data
This probably sounds dumb - I have a spreadsheet that I have imported data from an existing dsn/odbc, I set the properties to refresh every 24 hours (1220 minutes), and on open. The file is going to be used for an orgchart in another program that imports the data from excel. I am testing that automation also, it has tasks that refresh the data from the excel file. What I don't know is if the excel spreadsheet has to be open for the refresh to occur? That is not a problem, so far, but not sure yet if I can refresh the orgchart file if the excel file is open. The files will all be...

Conditional formulas with sum and if
Please help me this formula is driving me nuts. I am trying to add multiple ranges of fields, then taking the total and multiply by tenant square footage, then divide by gross leasable area, then take that total and multiply by an admin fee......NOT DONE YET.....then take that total and check it with an if statement....is this total <K55 if so enter the total, if false enter the value from field K55. So far this has stumped multiple IT personnel...PLEASE HELP! Here's an example of the formula: =-(sum(AC$215:AC$250+AC$252+AC$255)+(SUM B20:25+B30)*G55/J55*M55/12), if(<k55,[s...

Index DMV help needed
While I'm very aware of the plethora of scripts out there utilizing 2005/2008's dmvs to display underused, most used, & most needed indexes, I have the need to see if I can determine through internals exactly what indexes are being used by what procs. Meaning; if I have a database with 100 different stored procedures being called regularly, can I list what indexes have been used by these calls? My ultimate goal is to help know what indexes may need tuning the most by simply knowing it's used by many different procs calling it. I hope I'm making sense. Thanks i...

Please help 03-03-10
I need to timestamp a field when a user completes an audit in an audit date field. I have a form in datasheet view with all itmes to be audited on the form. The trigger for the completion of the audit is when the user selects "Rates Match" or "Rates do not match" from a combo box. A third category called PO pending is available in the combo box. When PO pending is selected I would like the timestamp field to be blank or null. I also need to disable the audit date field for any record where the user selects "Rates Match" or "Rates do not Mat...

Get External Data #4
I get Undefined Function <FunctionName> in Expression when I try t import data from MS Access (2k) into Excel (2k). The get external dat link works well with other result/datasets. In addition, excel does not allow me to add a reference to the acces database. Please let me know if someone has seen this issue before -- jned ----------------------------------------------------------------------- jnedd's Profile: http://www.msusenet.com/member.php?userid=337 View this thread: http://www.msusenet.com/t-187089572 ...

Excel vba autofilter code
I have some code to filter a result set to a list of unique names - is there something I can add that will also remove 'blank' entries ? Range("AP1:AP" & LastRowNewData).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("A44"), _ Unique:=True Thanks ...

x-axis data labels
Hello, I have 4 products w/3 yrs of data for each product. The x- axis label is the product and above that I would like to put the yr under each column. How can I do this? Help please... Thanks Michael - You can use multiple columns in the worksheet for category labels. This sample data: a b c Alfa 2000 10 11 12 2001 11 12 13 2002 12 13 14 Beta 2000 13 14 15 2001 14 15 16 2002 15 16 17 Gamma 2000 16 17 18 2001 17 18 19 2002 18 19 20 has the product names in the first column and the year in the second. Note that the cells atop these columns are blank; these blanks help sho...

Select query help
I have tblLog. It's fields are: Call: text Freq: single Mode: text CID: text QSL_R: text Credited: T/F I need the SQL to return all records where 'Credited' is false, 'QSL_R' <> NULL or blank, and whose combination of 'Freq' and 'Mode' for a given 'CID' have 'Credited' = false, but not if that record has a 'mate' whose 'CID', 'Freq/Mode', 'QSL_R' all match, and 'Credited' is True. I'm not sure if my description is understandable, so here is an example. Call Freq Mode...

help please 10-25-07
On Form received an error that led me back to the is line in my sql: REVNUM GRID = list. 10 column(5, list 10. list index + 1) I was told from post to make the REVNUM GRID a String. How is this done?? -LA http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/49863/List-Form-SQL-Hel p#7a1f7cb4b291cuwe -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200710/1 Not enough info to help. Please describe what you are trying to do. -- Dave Hargis, Microsoft Access MVP "misschanda via AccessMonster.com" wrote: > On Form ...

All accounts locked 2003r2 domain, help
Some hours after a migration from NT4 to 2003 interim we found out that all the domain admin accounts are LOCKED and that a specific workstation was the culprit of the locks(too late for that). even te sealed "administrator" domain admin account was locked up. How can i unlock the accounts if i don't have anymore administrative accounts? the DCs are 2003 R2 Hello Guillermo, The default domain administrator account should unlock at that moment when the correct password is used, see also: http://blogs.dirteam.com/blogs/jorge/archive/2006/10/05/The-Defaul...

Query to unnormalize some data
I have a the table with fields [FamNo], [SeqNo], [Name]. It is normalized. I want to select out [SeqNo]= 1 or 2 (easy enough), and create two new variables [NewName1] and [NewName2], so each record has both the 1 and 2 Names. Any ideas? And if this matters, the query needs to be based on a 2 linked tables (on FamNo). On Wed, 17 Mar 2010 20:53:39 -0400, "Joseph Greenberg" <jjgfromnj@gmail.com> wrote: >I have a the table with fields [FamNo], [SeqNo], [Name]. It is normalized. I >want to select out [SeqNo]= 1 or 2 (easy enough), and create two new >...

How to insert = in formula bar
Already this problem is posted but not answered for the right solotion? There use to be = button in the formula bar in Excell 2000 its not in excell XP, I tried with the = button from the toolbar but it did not worked as it did in excell 2000. How about clicking the fx symbol to the left of the formula bar? -- HTH Bob Phillips "Tirtha Raj Adhikari" <Tirtha Raj Adhikari@discussions.microsoft.com> wrote in message news:C02F4277-B8AC-4BE3-897A-368B2C6FFDD0@microsoft.com... > Already this problem is posted but not answered for the right solotion? > There use to be = b...

area code
This is a multi-part message in MIME format. ------=_NextPart_000_001B_01C66676.43625650 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I worked fine with Outlook until I bought a Nokia 6265 and tried to add = all my contacts to the phone list. the problem is that I used to write = phone numbers in (yy) xxxx-xxxx format, and when I synchronized them the = result was a 'yyxxxxxxx' number on the phone, which would not dial. If I can add a '0' before area code (0yyxxxxxxxx), or even better, = remove '(yy)' from...

Deleting demo data from database?
MS Small Business Server MS CRM 1.2 SQL 2000 I want to delet all demo data from my database and want to enter new data in the database. How can i do this? Awan There is no easy way to do this. You can try deleting records manually via the UI, but if you have a alot of data, this will take awhile. Another, though unsupported, approach would be to set the DeleteionStateCode field in every table to 2. This will allow the deletion service to remove the records. Or, you can try manually deleting from each of the tables (again, unsupported). Just be careful not to delete records from the CR...

formula or code help
Help please� We have meetings every month. On Sunday�s and Wednesday And every month we generate a task list for Sunday�s and Wednesday�s or vise versa it all depends in the month. For example on November I would have started on a Wednesday. For December I will start on a Sunday. But it�s only for a month at a time. A6=Data!F8 A7=IF(TEXT(A6,"dddd")="Sunday",A6+3,IF(TEXT(A6,"dddd")="Wednesday",A6+4)) A8=IF(TEXT(A7,"dddd")="Sunday",A7+3,IF(TEXT(A7,"dddd")="Wednesday",A7+4)) A9=IF(TEXT(A8,"dddd")="Su...

Help with Outlook and Messenger 7.0 and XP Firewall
I currently have the XP Firewall activated and am running Outlook 2002 and MSN Messenger 7.0. I noticed in the firewall exceptions that Outlook is not checked as an exception, although Messenger 7, Console and other items are. I am using ICS to share a dial up internet connection between 2 PC's that are connected thru wired Ethernet. I am getting my email OK. Also have a subscription to Hotmail Plus so I can read my hotmail account in Outlook, which also works. Recently I tried to sign out of passport and was unable to do so. I can sign out of all the other services, but it wil...

problem is getting data of type Memo from access
Hi, One of my database fields is of type Memo, (access database). I use Ado Data Bound Dialog to fetch the database records. But the data of the fields of Memo type are not retrieved. What is the problem with Memo data type? I need to store an array of length 8000, is there another way to store it in database? thanks, Behzad Look at the GetChunk/AddChunk methods. -- ============ Frank Hickman Microsoft MVP NobleSoft, Inc. ============ Replace the _nosp@m_ with @ to reply. "behzad" <b@b.com> wrote in message news:O7kS77C6EHA.4008@TK2MSFTNGP15.phx.gbl... > Hi, > ...

Adding same data to a field in many records
I have a table of contacts that are given 'types' via a look up table, e.g. Speaker, Audience etc and a second table of events that these contacts attend - a third table records which contacts attend which events (a many to many relationship). I would like to be able to choose all my audience types for example from a set county and in one operation assign them to one event, rather than having to assign them individually! Is this possible? I have a query that pulls out my contact types with the relevant attendance field ready to receive the Event ID but thereafter I am stuck - al...

help, problem.
can anyone help me to slove this problem? 1) I want to set an equation instead of input: =A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+....+A150*B150 thank you Try: =SUMPRODUCT((A2:A150)*(B2:B150)) -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com ---- "Peter Ho" wrote: >can anyone help me to slove this problem? >1) I want to set an equation instead of input: > =A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+....+A150*B150 >thank you "Max" <demechanik@yahoo.com> wrote... >Try: =SUMPRODUCT((A2:A150)*(B2:B150)) .... If b...

Help, Multiple conditional calculation
I have a question about multiple conditional calculation. Here is my question. A B C D E F A1 B1 C1 D1 =Max(A1:D1) A2 B2 C2 D2 =Max(A2:D2) A3 B3 C3 D3 ... A4 B4 C4 D4 ... I have a database like row 1-4 and column A to D. (The actual database is much bigger.) I konw how to get the maximum value as I write above. My question is how to get the location of the maximum number in F colum? For example, if in E3, the maximum number is B3, then F should be B3. I have checked with the excel help, but just find a "if function" with two condi...