count unique values with auto-filter on

Hello,

I'm trying to count unique numbers in a resulting list after filtering. I 
have already learned how to count unique number in an unfiltered list using:

=SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)>0,1))

but now I need to know how many unique numbers remain after I filter for 
another column. Help!

Cisco
0
Utf
1/5/2010 2:50:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1503 Views

Similar Articles

[PageSpeed] 50

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A12:A40,ROW(A12:A40)-ROW(A12),0,1)),MATCH(A12:A40,A12:A40,0)),ROW(A12:A40)-ROW(A12)+1)>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.

-- 
Biff
Microsoft Excel MVP


"Francisco Rodriguez" <FranciscoRodriguez@discussions.microsoft.com> wrote 
in message news:4F685ABC-5A47-4DC0-ADF2-FCEA18E73B19@microsoft.com...
> Hello,
>
> I'm trying to count unique numbers in a resulting list after filtering. I
> have already learned how to count unique number in an unfiltered list 
> using:
>
> =SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)>0,1))
>
> but now I need to know how many unique numbers remain after I filter for
> another column. Help!
>
> Cisco 


0
T
1/5/2010 3:07:52 AM
That worked like a charm. Thanks for your help and expertise!

Francisco

"T. Valko" wrote:

> Try this array formula** :
> 
> =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A12:A40,ROW(A12:A40)-ROW(A12),0,1)),MATCH(A12:A40,A12:A40,0)),ROW(A12:A40)-ROW(A12)+1)>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.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Francisco Rodriguez" <FranciscoRodriguez@discussions.microsoft.com> wrote 
> in message news:4F685ABC-5A47-4DC0-ADF2-FCEA18E73B19@microsoft.com...
> > Hello,
> >
> > I'm trying to count unique numbers in a resulting list after filtering. I
> > have already learned how to count unique number in an unfiltered list 
> > using:
> >
> > =SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)>0,1))
> >
> > but now I need to know how many unique numbers remain after I filter for
> > another column. Help!
> >
> > Cisco 
> 
> 
> .
> 
0
Utf
1/5/2010 2:28:01 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Francisco Rodriguez" <FranciscoRodriguez@discussions.microsoft.com> wrote 
in message news:3C0F7DEB-E697-4BE2-84E5-C2A70B6A001B@microsoft.com...
> That worked like a charm. Thanks for your help and expertise!
>
> Francisco
>
> "T. Valko" wrote:
>
>> Try this array formula** :
>>
>> =SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A12:A40,ROW(A12:A40)-ROW(A12),0,1)),MATCH(A12:A40,A12:A40,0)),ROW(A12:A40)-ROW(A12)+1)>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.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Francisco Rodriguez" <FranciscoRodriguez@discussions.microsoft.com> 
>> wrote
>> in message news:4F685ABC-5A47-4DC0-ADF2-FCEA18E73B19@microsoft.com...
>> > Hello,
>> >
>> > I'm trying to count unique numbers in a resulting list after filtering. 
>> > I
>> > have already learned how to count unique number in an unfiltered list
>> > using:
>> >
>> > =SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)>0,1))
>> >
>> > but now I need to know how many unique numbers remain after I filter 
>> > for
>> > another column. Help!
>> >
>> > Cisco
>>
>>
>> .
>> 


0
T
1/5/2010 4:30:01 PM
Reply:

Similar Artilces:

Counting current form
I have a problem I would like solving ,but a I am not sure where to start. I have 20 rows and 40 columns of three definite results. 1 2 3 4 5 6 7 8 9 10 11 12............40 John J j K j m M m M k K J J Martin k K K j M K J j J k K K Peter J j J k K K k K K j M K I would like to count the highest consecutive run of K or k and the highest consecutive run of J or j. I tried the CountIf function but as I said I do not where to start. Thanks Assuming the codes are in the same row =MAX(MMULT(--(A5:K5=A13),(TRANSPOSE(ROW(INDIRECT("1:"&COUNT(--(A5:K5=A13)))) )>=ROW(INDIR...

Cell Value vs Status Box Value
The cell value is not the same as the vlaue in the status box, e.g., status box shows 6147; cell value shows 64147 (format was with commas). Any suggestions/ideas? --- Message posted from http://www.ExcelForum.com/ awarner1 <<awarner1.11hdg3@excelforum-nospam.com>> wrote in news:awarner1.11hdg3@excelforum-nospam.com: > The cell value is not the same as the vlaue in the status box, e.g., > status box shows 6147; cell value shows 64147 (format was with commas). > Any suggestions/ideas? What is the number format of the cell? -- My email address has an extra @ (spell ...

Auto Accept Agent Errors
Hi Everyone, I am running the Auto Accept Agent on an Exchange 2003 w/SP1. I have about 10 resource mailboxes registered, and it had been working fine for almost a year now. Starting last week, the Agent stopped proccessing any new appointments, and is logging the following error about 8 times a second in the Application Log: -------------- Event Type: Error Event Source: Auto Accept Agent Event Category: None Event ID: 4097 Date: 10/25/2005 Time: 8:44:08 AM User: N/A Computer: ODIN Description: Exception occurred while processing item "". This item will not be processed. T...

Auto-Receive Freight, Tax & Miscellaneous charges from PO
When doing a Shipment/Invoice receipt in Dynamics on a PO, when the Auto-Receive button is selected, the system does not bring in the amount in the subtotal area for Freight, Taxes or Miscellenous charges. This requires us to alway go back to check the original PO to verify the amounts are correct before processing and posting the receipt. There should be an option in the Purchasing setup to allow to receive the complete PO, if you are not receiving multiple POs on one receipt. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions wit...

remove filter on a form when closing
Hi All, I have form "frmRecipes" that can be called from two different places: from a button on a form named "frmUserRecipes" and from a button on the main switchboard. From "frmUserRecipes" I use this code to take me to a specific record: DoCmd.OpenForm "frmRecipes", acNormal, , "FoodCode=Forms!frmUserRecipes!OLDRNUM" This works fine. If I use the button on the switchboard I get prompted for "Forms!frmUserRecipes!OLDRNUM" when "frmRecipes" opens. I need it to open on the first record in this instance - no filter. I...

counting rows depending on 2 selective values
Hi, well, I have lots of data and need to do some statistics on it. For that I need to count the number of rows "H" where a string value "X" is written but only if these rows also have cell which is NOT empty in column "E"... the if stuff drives me crazy (especially as i have a spanish version...) any help appreciated :) -- Acid-Sky ------------------------------------------------------------------------ Acid-Sky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26502 View this thread: http://www.excelforum.com/showthread.php?thr...

Pivot Chart Filter
I have two Pivot charts that compare similar data and have them displayed side-by-side. When I change the filters in one chart, I want to apply the same filter for the other chart. Is there a way to have one Pivot Chart Filter Pane control two or more Pivot Charts without having to click each chart and change to filter for each one? ...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Auto-Responders & Address Delimiters
I have two questions. 1) Is there any why to put a auto-reply rule on a timer, or otherwise automate when it auto-replies by date/time? Basically, I need to have a auto-reply sent if a message is delivered outside normal operating hours. I suggested manually turning on a auto-reply rule when the last person leaves, but was told that it would be preferred if it was automated. 2) Is there any sane why to have Exchange use/accept delimiters? A couple people have asked if we could something along the lines of user+something@company.com. Essentially, something similar to what some web mail...

Counting NON-Blank fields
I have two columns of data that I export from MS-Access into Excel and then paste into another excel spreadsheet. Column A is my description; Columns B and C are my values. Both Column B and Column C have 50 rows. (This number varies) Column B has some blank rows. Column C has data in all rows (always). I need to count the number of records in each column. Using the CountA command, I currently first have to select any blank field and delete the contents as Excel is reacting as if they are not blank. Using the CountBlank command it correctly counts the blank fields, howe...

Filter Form Records button
I'm working with a form that displays all records, but has a filter button to show only a certain year, or all (*). Once prompted for the year in the messagebox, hitting cancel returns me to the form but with NO records showing. How can I hit cancel and have it go back to the form, displaying whatever records it was before hitting the filter button? Here's my current button code: ------------------------------------------------------------------------------ Private Sub btnFindStyle_Click() On Error GoTo Err_btnFindStyle_Click Dim msg, Style, Title, myResponse msg = "Enter Year...

filtering colums
Can anyone tell me how I filter a column, so it shows the entries that are not present in a second column? say: column1: column2: 1 2 2 5 3 4 5 How can I get a third column showing: 1 3 4 Is this possible in Excel??? Thx.. Can you use a third column and filter on that? I'd put this in C2 (headers in Row 1): =isnumber(match(a2,b:b,0)) and copy down Then filter to show the False's in that column. benny wrote: > > Can anyone tell me how I filter a column, so it shows the entries that are > not present in a second column? say: ...

counting negative and positive percentages in a column
I am trying to figure out how to do a sumif or countif for a range of values negative and positive values of a cell in a column. example 10% 15% 33% -10% -5% -45% I would like to have one formula to count the cells with a positive % from 0.01% to 20% another formula to count the cells with a negative % from -0.01% to -20% thank you Try these: For the positive range: =COUNTIF(A1:A10,">=0.01%")-COUNTIF(A1:A10,">20%") For the negative range: =COUNTIF(A1:A10,"<=-0.01%")-COUNTIF(A1:A10,"<-20%") -- Biff Microsoft Excel MVP <vieux-lo...

Report Filtering
I just added CRMAF to my query and now I get no results whatsoever no matter what filter I use. Has anyone seen this before and is there any way around it. I have a report with 3 subreports in it. All I wanted to do was be able to filter the main report but now I get nothing. Any suggestion will be appreciated. It looks there is something wrong with the filters. The common problems could be conflicting filters, for example one filtered defined using CRMAF and other filter was a static one defined in reports. You can do a SQL trace using SQL profiler. It will show up the final SQL st...

Auto-fill Price List and Unit of Measure? 07-02-04
Does anyone know how to automatically fill the Price List field when a customer is selected for Quote or Order with the Customer's default price list? Same question for default Unit of Measure when a product is selected? michael@engcc.com If you create the Quote/Order within the Account/Contact that has a Price List associated, then it will default it. Frank Lee Workopia, Inc. http://www.workopia.com San Francisco, CA >-----Original Message----- >Does anyone know how to automatically fill the Price List >field when a customer is selected for Quote or Order with >th...

filter and sorting
I am trying to perform a ascending sort and keep getting an error ... this operation requires the merged cells to be identically sized. I have a table 3 top rows are the header with various merged cells, then I have a variable number of data rows (no merged cells), and finally there are several footer row with various merged cells. I am needing to sort the data rows, how can I do this? QB If you carefully select the data rows range between your upper and lower merged cells you can sort that range. Best to try to do without merged cells which cause these problems in the fi...

filter driver for an USB modem
Hello, I am working on a NDIS filter under Windows 7 64 bits. The filter is working well as NetService Class with classical Ethernet Adapters. But, it does not work with an USB Modem with "Modem" class. Packets handled by the modem are not sent to the filter. How can I get them ? I suspect I should register the driver in another manner. Regards. Eric Boudrand > Packets handled by the modem are not sent to the filter. How can I get = > them ? I suspect I should register the driver in another manner. You need to support PPP in your filter, this requires ...

what is the best way to do this (replacing xmlnode value)
i need to copy several xml files and use it as xml element and insert them into a xml file. i use DOM support in .Net and from the pervious help i was able to copy and insert them into the xml file that i want but i need to change some xml node value before i insert. what is the best way to do this? dotnetnoob wrote: > i need to copy several xml files and use it as xml element and insert them > into a xml file. i use DOM support in .Net and from the pervious help i was > able to copy and insert them into the xml file that i want but i need to > change some xml node value ...

Complex (for me) checking values in fields to perform calcs in others
Hi all.. I am creating a Cash Flow Projection report in access that inspects the "completed dates" of sheduled draws to calculate a remaining balance, but I am having problems with it. The idea is: --------------------- Iff [Draw1CompletedDate] = Null then Me!txtBalance = [MortgageAmount] Else If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] = Null then Me!txtBalance = [MortgageAmount] - [Draw1Amount] Else If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND [Draw3CompletedDate] = Null then Me!txtBalance = [MortgageAmount] - [Draw1Amoun...

Auto Import Into Deny List
Hi, Is their any way of importing the IP's in a text file into the deny email from without having to manually add the Ip's one at a time http://www.bubbleshare.com/album/135453 Thanks Try ipsec.vbs http://www.microsoft.com/downloads/details.aspx?familyid=286d2818-08b3-496d-8b0d-c2b628a3ef16&displaylang=en James Chong (MVP) MCSE | M+, S+, MCTS, Security+ msexchangetips.blogspot.com On Mar 12, 3:27 pm, melu <m...@discussions.microsoft.com> wrote: > Hi, > > Is their any way of importing the IP's in a text file into the deny email > from without having to...

Find and replace values across multiple fields
I would like to find and replace several values in my Access table.For example, I would like to change all "%40" to "@", but rather thanspecify a single field/column of my table to search through, I wouldlike the code to search through all of the fields in my table (thereare too many replacements and too many field names to specify).How can I modify the code below so that it searches through each fieldin the table?Public Function RunCleanValues() DoCmd.RunSQL "UPDATE [mytable] SET [fieldname] =Replace([fieldname],'%40','@');"End FunctionThank yo...

Auto Accept Agent #5
I've been testing this. There is one thing I don't like. If you invite a conference room as a resource and the resource does not accept, the invitations still go out to the recipients. Is there a way to configure the agent to stop this so if the resource is not available the invitations don't go out. No. If you send out a regular meeting request and one person rejects it, the entire meeting doesn't get cancelled automatically, does it? Why should auto accept behave any differently? Remember that all it is doing is automatically accepting/rejecting a meeting reques...

How can I initialize the value?
template < class elemType > class MyArray { public: explicit MyArray( int size = DefaultArraySize ); MyArray( elemType *array, int array_size ); MyArray( const MyArray &rhs ); virtual ~MyArray() { delete [] ia; } bool operator==( const MyArray& ) const; bool operator!=( const MyArray& ) const; MyArray& operator=( const MyArray& ); int size() const { return _size; } virtual elemType& operator[](int index) { return ia[index]; } virtual void sort(); virtual elemType min() cons...

Can you make excel auto save your workbook?
I need Excel to automaticly save my work every 2 or 3 minutes if that is possible. Can it do that? Hi what Excel version are you using? -- Regards Frank Kabel Frankfurt, Germany "Rhiannon" <Rhiannon@discussions.microsoft.com> schrieb im Newsbeitrag news:FE6930C8-D9D4-46D0-825E-0C25A02C1B71@microsoft.com... > I need Excel to automaticly save my work every 2 or 3 minutes if that is > possible. Can it do that? Excel 2003 "Frank Kabel" wrote: > Hi > what Excel version are you using? > > -- > Regards > Frank Kabel > Frankfurt, Germany &g...

Auto-Transfer of sales documents with eConnect
Great Plains eConnect should be enhanced with an object that will allow for the transferring of sales documents from one type to another (i.e. Order to Invoice). This feature would greatly improve efficiency for clients doing high volumes of transactions using the Great Plains distribution modules. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microso...