Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 20

Thread: Count Unique Items In Autofilter

  1. #1
    Join Date
    4th June 2007
    Posts
    49

    Count Unique Items In Autofilter

    Hi All

    I have a Excel sheet and I have put Autofilter for a particular category I choose I need to count number of items in another column(autofilter) , for eg... there are two columns Category , Items When i choose a particular Category I need to count number of Items in the autofilter for that particular category??? How can I do this???

    regards

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th April 2006
    Posts
    224

  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Count Items In Autofilter

    Hi,

    =SUBTOTAL(3,YourRange)

    HTH

  4. #4
    Join Date
    4th June 2007
    Posts
    49

    Re: Count Items In Autofilter

    Hello

    Thanks for the reply. Well in my above example I have limited to a situation where I choose a particular Cateogry(from Autofilter) and I need to count the number of unique items column(autofilter) and then I need to find the average how can I do that???

    regards

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Count Items In Autofilter

    Hi,

    Assumptions..

    Filter Col: Col A

    Unique Count Col: Col B

    In F1,

    =LOOKUP(2,1/(Ary=1),A2:A25)

    where Ary is a define name.

    =SUBTOTAL(3,OFFSET(Sheet1!$B$2:$B$25,ROW(Sheet1!$B$2:$B$25)-ROW(Sheet1!$B$2),,1))

    In H1,

    =COUNTDIFF(IF(A2:A25=F1,B2:B25,"#"),FALSE,"#")

    To Enter the array formula hold down Ctrl and Shift while pushing Enter.

    You must have MOREFUNC Add-In

    See the attachment.

    HTH
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Dave Hawley; June 13th, 2007 at 13:04.

  6. #6
    Join Date
    4th June 2007
    Posts
    49

    Re: Count Unique Items In Autofilter

    Hello Krishna

    Thank you very much but the Col: H shows #NAME?

    How to overcome ???

    regards

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    27th January 2003
    Location
    Auckland, New Zealand
    Posts
    396

    Re: Count Unique Items In Autofilter

    You must have MOREFUNC Add-In

    See Krishnas link above
    Kind Regards,
    Ivan F Moala From the City of Sails

    http://www.xcelfiles.com

  8. #8
    Join Date
    4th June 2007
    Posts
    49

    Re: Count Unique Items In Autofilter

    Thanks Man..

    Special Thanks to Krishna

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    4th June 2007
    Posts
    49

    Re: Count Unique Items In Autofilter

    Well I need a function where I can Count Items in the auto filter?? in the example =SUBTOTAL(3,YourRange)

    Now YourRange is a very large range.

    regards

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    4th June 2007
    Posts
    49

    Re: Count Unique Items In Autofilter

    Hi and Hello

    This works very great ...but I have found a problem say supposing if there are null values in Col : A and Col : B using Autofilter->Custom Not eqal = Null I set so that..I don't get items with null values. But the formula will also count number of items which has null values that means it is not counting the number of unique values from filter but from the range what i have defined ?? How can count only items in the auto filter after setting parameter ?? for example select Test10 from autofilter A:col see there count no of items in col:b ??

    regards
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Count Items in Autofilter
    By fengshui in forum Excel General
    Replies: 10
    Last Post: May 28th, 2006, 22:05
  2. Count unique items AND specify range conditions
    By majormike in forum Excel General
    Replies: 4
    Last Post: May 16th, 2006, 09:17
  3. Count unique items and/or consolidate items in a list...
    By willie447 in forum Excel General
    Replies: 1
    Last Post: September 19th, 2005, 23:08
  4. Using SUMPRODUCT to count unique items
    By dirtyfrog in forum Excel General
    Replies: 9
    Last Post: August 10th, 2005, 18:59
  5. Replies: 9
    Last Post: October 27th, 2004, 00:14

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno