Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Count Unique Items In Autofilter

1. Member
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. ## Re: Count Items In Autofilter

Excel Video Tutorials / Excel Dashboards Reports

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

## Re: Count Items In Autofilter

Hi,

=SUBTOTAL(3,YourRange)

HTH

4. Member
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. Super Moderator
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.

See the attachment.

HTH
Last edited by Dave Hawley; June 13th, 2007 at 13:04.

6. Member
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. ## Re: Count Unique Items In Autofilter

8. Member
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. Member
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. Member
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

Excel Video Tutorials / Excel Dashboards Reports

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

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