# 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

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

See the attachment.

HTH

## Files

• Re: Count Unique Items In Autofilter

Hello Krishna

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

How to overcome ???

regards

• Re: Count Unique Items In Autofilter

Thanks Man..

Special Thanks to Krishna

• 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

## Files

• 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

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

[ea]*[/ea]

• 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

Sorry Forgot to Attach (previous post)

## Files

• 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

Hi Fin Fang

Well I followed the instruction given by you but it only Hows 0 in H:Col. I have attached the excel sheet again.

regards

## Files

• 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

## Files

• Re: Count Unique Items In Autofilter

Hi,

I think Fin is offline.

Try,

= SUM(IF(FREQUENCY(IF(SUBTOTAL(3, OFFSET(C2:C99,ROW(C2:C99)-ROW(C2),0,1)),IF(E2:E99<>"NULL",IF(D2:D99<>"NULL",MATCH(C2:C99,C2:C99,0)))),ROW(B2:B99)-ROW(B2)+1)>0,1))

[ea]*[/ea]

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