Count Unique Items In Autofilter

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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

    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

    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,"#")


    [ea]*[/ea]


    You must have MOREFUNC Add-In


    See the attachment.


    HTH

    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

    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

    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))

    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

    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))

    Re: Count Unique Items In Autofilter


    Ah my mistake. Try...


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

    Re: Count Unique Items In Autofilter


    Thanks Very much Fin and also to Krishna Kumar .


    Fin I have another question to the same thread. How can I count type(field) with the below condition


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


    Attached is a excelsheet.


    regards

    Re: Count Unique Items In Autofilter


    Hi Krishna Thanks Very Very much Man. You have been very very helpful even on other post of mine....


    Thanks.. it works..I shall get back if something is not...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login