Autofilter and FREQUENCY

Hi!

I have this FREQUENCY formula in my srpeadsheet:

=FREQUENCY(P1:P40000;S1:S10)

If I autofilter the list, the formular will still display the results of all 
rows. Is there away to get FREQUENCY only to count Filtered Rows? Or can the 
same result be achieved any other way.

I thought of combining with the SUMPRODUCT() function, but as the arrays are 
not identical in siz, this will not work.

Jan 


0
Jan
12/6/2007 1:39:34 PM
excel 39879 articles. 2 followers. Follow

3 Replies
79 Views

Similar Articles

[PageSpeed] 56

jAN,

tRY THIS

=FREQUENCY(IF(SUBTOTAL(3,OFFSET(P1,ROW(INDIRECT("1:4000")),0)),P1:P4000),S1:S10)

-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message 
news:OsI4r1AOIHA.4880@TK2MSFTNGP03.phx.gbl...
> Hi!
>
> I have this FREQUENCY formula in my srpeadsheet:
>
> =FREQUENCY(P1:P40000;S1:S10)
>
> If I autofilter the list, the formular will still display the results of 
> all rows. Is there away to get FREQUENCY only to count Filtered Rows? Or 
> can the same result be achieved any other way.
>
> I thought of combining with the SUMPRODUCT() function, but as the arrays 
> are not identical in siz, this will not work.
>
> Jan
> 


0
bob.NGs1 (1661)
12/6/2007 2:14:09 PM
Sorry, correction

=FREQUENCY(IF(SUBTOTAL(3,OFFSET(P1,ROW(INDIRECT("1:40"))-1,0)),P1:P40),S1:S10)

-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message 
news:OsI4r1AOIHA.4880@TK2MSFTNGP03.phx.gbl...
> Hi!
>
> I have this FREQUENCY formula in my srpeadsheet:
>
> =FREQUENCY(P1:P40000;S1:S10)
>
> If I autofilter the list, the formular will still display the results of 
> all rows. Is there away to get FREQUENCY only to count Filtered Rows? Or 
> can the same result be achieved any other way.
>
> I thought of combining with the SUMPRODUCT() function, but as the arrays 
> are not identical in siz, this will not work.
>
> Jan
> 


0
bob.NGs1 (1661)
12/6/2007 2:16:26 PM
Thank You. That did it :-)

Jan


"Bob Phillips" <bob.ngs@somewhere.com> skrev i en meddelelse 
news:uGBIXKBOIHA.6060@TK2MSFTNGP05.phx.gbl...
> Sorry, correction
>
> =FREQUENCY(IF(SUBTOTAL(3,OFFSET(P1,ROW(INDIRECT("1:40"))-1,0)),P1:P40),S1:S10)
>
> -- 
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my 
> addy)
>
> "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message 
> news:OsI4r1AOIHA.4880@TK2MSFTNGP03.phx.gbl...
>> Hi!
>>
>> I have this FREQUENCY formula in my srpeadsheet:
>>
>> =FREQUENCY(P1:P40000;S1:S10)
>>
>> If I autofilter the list, the formular will still display the results of 
>> all rows. Is there away to get FREQUENCY only to count Filtered Rows? Or 
>> can the same result be achieved any other way.
>>
>> I thought of combining with the SUMPRODUCT() function, but as the arrays 
>> are not identical in siz, this will not work.
>>
>> Jan
>>
>
> 


0
Jan
12/6/2007 10:34:09 PM
Reply:

Similar Artilces:

Access 2007 -- Disable autofiltering in datasheets?
Hello Everyone, I just stumbled onto something interesting and was wondering if anyone else has experienced it and how they fixed it. I've got a datasheet subform developed in an Access 2003 database. Users can click on a row and delete the record on the datasheet. However, when we open the database using Access 2007, Access 2007 sets autofiltering on each column, so I can't select the row--only columns. I can't seem to find how to disable this feature in the Access 2007 Options area. Thanks for your help. will be interested if someone knows I don't have datasheet as subf...

Custom axis label frequency
Hello, I've been creating a spreadsheet with the daily value of my stock portfolio, for personal records. Within the spreadsheet, I've created a chart that shows the daily progression as a line graph. What I want to do, is the X-axis (day value) display a label on the first trading day of each week. Right now I'm given the option of placing a label every number of days (ticks), but given that trading weeks vary in length due to holidays, it doesn't end up the way I want. I'm also not given the choice of starting after a few days, so the first label will always be on the fi...

how do i plot a rose diagram to show frequency of wind direction
can i use excel or another program in microsoft to do this? Hi Kathryn, Get a wind rose for Excel here http://edferrero.m6.net/content/windrose.zip Ed Ferrero http://edferrero.m6.net "kathryn" <kathryn@discussions.microsoft.com> wrote in message news:71874D4A-0D49-4490-BAE1-2C74F9E876FC@microsoft.com... > can i use excel or another program in microsoft to do this? ...

Crosstabs for non-numeric value frequencies
I've got some survey data in which respondents chose among different text descriptors. I want to create a table where the frequency of the different responses are tallied by a couple of different grouping variables. Can I use a pivot table for this? They seem to assume quantitative data. Regardless of the answer to this question, can you provide a hint as to the most straightforward method of creating the crosstab tables? TIA, David Hi, Excel has replaced the CrossTab tool with the Pivot Table tool, in fact they did that in version 5 if my memory serves me. You should use a pivot t...

No "Sort A-Z" in autofilter in Excel (Office XP)
Hi all, As always, thanks in advanced. Here's my deal: I use to have "Sort A-Z" when I "autofiltered" a row of information designated as info categories on my OS X version of Office, but it doesn't exist in the Office XP version of Excel. You know how I get add or properly execute this function. The usual sort button won't work because it sorts all the rows in the column, whereas I need only sort all the information below a certain row. Cheers, Jim Lee If you select just the rows you want to sort, it should work correctly. Or are you saying that it sorts al...

AutoFilter #5
How to return the Autofilter value into a specified cell Here is a UDF by Stephen Bullen that will do it Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen 'use like =FilterCriteria(A3) ' The single-cell argument for the FilterCriteria function ' can refer to any cell within the column of interest 'do not use the cell with the arrows, it will not update Dim Filter As String Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Col...

Autofilter
Is it possible to autofilter a single column within a project? I am using Project 2007. Thanks Larry Larry, I am not sure what you mean by Autofilter a single column. Can you give more detail as to what you are trying to do? "External Update" wrote: > Is it possible to autofilter a single column within a project? I am using > Project 2007. > Thanks > > Larry Hello Larry, Sure. Turn on Autofilters and use the drop-down filter only on the single field. The Autofilter icons will appear at each field heading, but you need not use them...

Autofilter problem #2
I have this quite annoying problem with autofilter because I work with large lists very offten. I read somewhere that autofilter is limited to 1000 rows. Is there a solution for this? Using custom filter doesn't help too much. Thanks. Hi see http://www.contextures.com/xlautofilter02.html#Limits -- Regards Frank Kabel Frankfurt, Germany dustystorm wrote: > I have this quite annoying problem with autofilter because I work > with large lists very offten. I read somewhere that autofilter is > limited to 1000 rows. Is there a solution for this? Using custom > filter doesn'...

why is autofilter not available (in light grey on drop down)
Autofilter was working on my spreadsheet but since I moved a line of data the function is not available. How do I restore Autofilter to a working condition? ...

Code to turn off autofilter
I am in the process of making several macros and I was wondering if there is code to place in the macro that can - if the autofilter is on, will turn autofilter off just prior to copying the work sheet? I am currently using the code in RDB_copy_sheet to copy the worksheets Thanks Hi Take a look at this: Sub aaa() If Worksheets("Sheet1").AutoFilterMode Then isOn = "Was on" Worksheets("Sheet1").UsedRange.AutoFilter Else isOn = "Off" End If MsgBox "AutoFilterMode is " & isOn End Sub Regards, Per "fgwiii" <fg...

Printing slected items after using autofilter
Hi can any please help me ? What I�m trying to do is to automate printing a shopping list via button and macro (code sorted for this ) printing a selected range fro my worksheet. What I have done is set an auto filter of the items I require and wha I want to do is to print the selected records, however this list ca get bigger or smaller dependant on the number of items selected. Any thoughts would be very much appreciated (Excel version 2000) Please see attached bitmap Many Thanks Ro +------------------------------------------------------------------- |Filename: shopping list.bmp ...

Counting cells with a specified frequency(how many times it appear
Hi, I want to sum up cells thats has any word appearing 10 times in the column. In the other words, the specified word can be anything. The end result is to the sum up cells that has any word appear 10 times in the column. eg. column 1 row 1 a two the row 2 two 924 the row 3 the desk to row 4 let's can to two if i want to sum up the cells that has any word appearing twice, the result will be 1 word frequency of 3, the answer will be 2 (row 1 - the & two, row 2, row 3 - the, row 4 two) Thanks ...

Autofiltering not working in certain columns
I have a spreadsheet with 15 columns and 130 rows. The autofilter drop list arrows work fine in some columns and in other columns they refuse to function. In the latter columns when youclick on a drop list arrow it just blinks, no list falls down. I have been unable to find any common reason why the autofilter list won' t appear in these columns. I am using Excel 2001 for Macintosh. Thank you for any help someone can provide. ...

Autofilter
Hi all, When I am using Excel's Autofilter, often the statusbar shows the number of selected rows as part of the total number of rows. Often, but not allways. How is this, and what can I do about it ? Tia, Bas Hartkamp. When your list has many formulas, or if a cell in the list is changed after a filter has been applied, the Status Bar may show "Filter Mode" instead of a count of the visible records. You can use the Subtotal function to count the visible rows. The following MSKB article has information: The AutoFilter status bar displays the "Filter Mode" me...

Autofilter list options
I am working with a worksheet that has the autofilter applied. The list does not reflect (Blanks) and (no blanks) and there are blank cells. How do I get this added to my list ->>>>>Help ASAP Thanks Maybe the empty cells actually have a space or something in them, or maybe the (Blanks) and (No blanks) options are at the other end of the drop down list. Vaya con Dios, Chuck, CABGx3 "red1" wrote: > I am working with a worksheet that has the autofilter applied. The list does > not reflect (Blanks) and (no blanks) and there are blank cells. How do I ge...

Autofilter Wildcards Not Working on Number Column
Hello everyone...n00b question. I'm using Excel 2003 to split up a daily spreadsheet between me and 2 of my peers. We split the orders based on the last 2 characters of the order. The orders are all 7 digits and all numerical. Exp... 3215453. For example... my number range is 00-33. Every morning I have to scroll through the spreadsheet and manually select my orders to obtain my daily workload, which can take up to 40 minutes. I tried using auto-filter with wildcards * & ?, but they don't work. I'm typing my custom filter values as follows ?????33 and *33, but they don't ...

how to make a histogram with frequency
how do you use the histogram function?i don't really understand bins. all i have in my data are income groups e.g 0-10k, 11-20k etc and the frequency of each one. can i make a histogram with this data? hannah553 - > how do you use the histogram function?i don't really understand bins. all > i have in my data are income groups e.g 0-10k, 11-20k etc and the > frequency of each one. can i make a histogram with this data? < To use the Histogram tool, the data range is the raw data on a worksheet, and the bins are values you enter in the worksheet to specify the up...

removing autofilter sort options
Excel 2003. When applying autofilter to the column, autofilter dropdown list first entries are sort ascending and sort descending. With earlier Excel versions 1st entry used to be All and I used home key to turn filter off (to select all). It was handy especially when there was a long autofilter list to scroll up. Can I remove these obsolete entries from autofilter drop down list? (Obsolete to me, as I haven't even figured out, why these are there. And more, when I had tried this sorting option several times I noticed that it had badly messed up the whole worksheet, where normally onl...

AutoFilter #2
When using AutoFilter, is it possible to have the text value of the filter selected inserted into a cell? I am using Excel to evaluate survey results. I have a worksheet set up so that filters can be applied to different columns to look at the data in a variety of ways. The results of those filters are used to calculate percentages, such as percent favorable responses from respondents fitting a certain criteria. Those results are displayed on a separate worksheet that updates as different filters are applied. However, I don't know how to get that worksheet to display the fil...

price history frequency
I have MS Money 2004 standard version. I noticed the investment price history only shows end of week prices not daily price history. Is there a way to get daily price history for every day. I believe that the only way you can do this is by downloading the closing price each business day. This will allow you to build up a daily history starting now. The only way to get a daily history prior to today would be to manually enter the prices. "handydan" wrote: > I have MS Money 2004 standard version. I noticed the investment price > history only shows end of week prices...

Can you use autofilter and a graph together?
I'm trying to use autofilter to select a single line of data (the uppermost) then graph it. Unfortunately When I select the source data for my graph (A4 through IH4) then use autofilter it doesn't select the top row of data, instead it still graphs the same row targeted before the filter process. How do I get it to graph the row beneath my autofilter drop down list? In a nutshell, can you use autofilter to select the line of data you wish to graph? Thanx in advance! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and pos...

Autofilter Criteria Problem
When I try to insert a criteria in an Autofilter using a macro, NO results are shown. Even when I record the macro, and it works perfectly while recording, when I run the macro; No results are shown. I used the following code: Selection.AutoFilter Field:=5, Criteria1:=">29-4-2003", Operator:=xlAnd, _ Criteria2:="<5-5-2003" Can anybody see the problem (or even beter; the sollution)? Thanks in advance! Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:=">2001/01/03", Operator:=xlAnd _ , Criteria2:="<2001...

Autofilter not working for cells with data validation
Hi all I have a long column of cells that have the same data validation lists applied to them. At the top of the list is an auto filter. However when I go to use the autofilter and select of the options it doesn't work...grrrrr. please help!!! thanx in advance Freddo Frog Freddo What is "doesn't work"? I just tested 5000 rows in a column. Selected A1:A5000 and DV>List. Entered days of the week. Selected Sunday through Saturday in A1:A7 Copied down to A5000. Autofiltered on Monday in Column A and worked fine. Gord Dibben Excel MVP On Mon, 21 Jun 2004 21:30:39 -...

Where to setup receive frequency?
Outlook 2003 receives eMails automatically every minutes. Where do I change this receive frequency? Sven Hi Sven, > Outlook 2003 receives eMails automatically every minutes. > Where do I change this receive frequency? You can change this in the menu 'Tools' > 'Options' > tab 'Mail Setup' > 'Send/Receive' Thomas Wetzel Synchronize your Outlook http://www.SynchPST.com Backup your Outlook http://www.BackupOutlook.com ...

Turning off the Autofilter in a Macro
Can someone please assist me - several people have assisted me with this but as I am not a programmer, I have not been able to figure out where to place the code to turn off the autofilter. Thank you for your help! <.AutoFilterMode = False> Sub RDB_Copy_Sheet() Dim myFiles As Variant Dim myCountOfFiles As Long myCountOfFiles = Get_File_Names( _ MyPath:="\\RADNTSHARE\Old_Share\share\4553AB7 (Xience V)\Data Management\Reports\ClinDart Reports", _ Subfolders:=True, _ ExtStr:="*20090727_Reviewed*...