Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Count Unique Items In Autofilter

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Count Items In Autofilter

    This link may help

    http://www.contextures.com/xlautofilter03.html#Count

    VBA Noob

    Comment


    • #3
      Re: Count Items In Autofilter

      Hi,

      =SUBTOTAL(3,YourRange)

      HTH
      Kris

      ExcelFox

      Comment


      • #4
        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

        Comment


        • #5
          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
          Last edited by Dave Hawley; June 13th, 2007, 13:04.
          Kris

          ExcelFox

          Comment


          • #6
            Re: Count Unique Items In Autofilter

            Hello Krishna

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

            How to overcome ???

            regards

            Comment


            • #7
              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

              Comment


              • #8
                Re: Count Unique Items In Autofilter

                Thanks Man..

                Special Thanks to Krishna

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: Count Unique Items In Autofilter

                      I'm not sure but you can try this.

                      No addin needed. Its an-array must hold down:

                      Ctrl,Shift,Enter

                      =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),IF(A2:A100=F1,IF(B2:B100<>"NULL",MATCH(B2:B100,B2:B100,0)))),ROW(A2:A100)-ROW(A2)+1)>0,1))

                      Comment


                      • #12
                        Re: Count Unique Items In Autofilter

                        =COUNTDIFF(IF(A2:A30=F1,IF(B2:B30<>"NULL",IF(B2:B30<>"",B2:B30),FALSE),FALSE),FALSE,FALSE)

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

                        ExcelFox

                        Comment


                        • #13
                          Re: Count Unique Items In Autofilter

                          Hi

                          Thanks for reply, I have tried to Apply the formula but I am confused, There is a problem ,

                          Please consider the Attached. GOTO Modified Sheet. There are 5 Fields

                          1 . Set Autofilter for Field 2 to "TOM" Count the Items in the Autofilter Field 3

                          The Count is 4

                          2. Set Autofilter - > Field 4 ->Custom - Does not Equal to NULL
                          3. Set Autofilter - > Field 5 ->Custom - Does not Equal to NULL

                          Now Count Number of Items in Field 2

                          The Count is 3.

                          now I need formula where I get count as 3.

                          regards

                          Comment


                          • #14
                            Re: Count Unique Items In Autofilter

                            Sorry Forgot to Attach (previous post)
                            Attached Files

                            Comment


                            • #15
                              Re: Count Unique Items In Autofilter

                              Hi phiprbh,

                              The formula below should give you what you want. Like I said before you don't need a add-in with this formula.

                              The formula is an-array copy the formula below paste it in cell H2 double click in that cell and hold down:

                              Ctrl,Shift,Enter


                              =SUM(IF(FREQUENCY(IF(SUBTOTAL(3, OFFSET(C2:C100,ROW(C2:C100)-ROW(C2),0,1)),IF(B2:B100=F1,IF(C2:C100<>"NULL",MATCH(C2:C100,C2:C100,0)))),ROW(B2:B100)-ROW(B2)+1)>0,1))

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X