# How to count unique values in filtered list?

1. ## How to count unique values in filtered list?

Hi together,

I have a list (colomn) which contains different values, multiple.
Now i use this formula to count the number of different contained values:
{=SUMME(WENN(HÄUFIGKEIT(WENN(LÄNGE(A2:A10)>0;VERGLEICH(A2:A10;A2:A10;0);""); WENN(LÄNGE(A2:A10)>0;VERGLEICH(A2:A10;A2:A10;0);""))>0;1))}

Now I'd like to count the number of different values by filtering the list by another value.
But unfortunately this formula ignores filtering, it counts also the hidden values.

How must I change this formula, that it counts only visible values?
Is there another way to solve this problem?

THX u all for helping me!
Cayenne

2. ## Re: How to count unique values in filtered list?

Hi,

Have you thought of using

Formula:
`Please Login or Register  to view this content.`

3. ## Re: How to count unique values in filtered list?

Subtotal (Teilergebnis) 102 tells you how many values are in an area.

BUT: I'd like to know how many "different" values ar in there!

For instance, there should be following data in: 1,2,3,4,4,5,5 - The wanted solution should be: 5
AND: If i filter off 5, then the solution should be 4.

I hope I could clarify!

BR, Cayenne

4. ## Re: How to count unique values in filtered list?

A sample workbook would be nice so we don't have to translate functions and regional settings...

5. ## Re: How to count unique values in filtered list?

Hi All,

``Please Login or Register  to view this content.``
to be confirmed withn control+shift+enter

Ich hoffe, es ist ein wenig Hilfe

6. ## Re: How to count unique values in filtered list?

Here is a sample workbook.

There are 6 different values in.
If I filter out the "4", you can only see 5 different values remaining.

BUT the formula counts 5 different values anyway.
How mut the formula be adjusted, that only the visble values are counted?

THX /C

7. ## Re: How to count unique values in filtered list?

Hi,

Using a "Helper Column" is my method for doing this kind of problem. See the attached.

8. ## Re: How to count unique values in filtered list?

Hi Canapone!

Could you be so kind and put your formula in an xls-upload?
Cause xls translates your formula automatically...

THXTHX C.

9. ## Re: How to count unique values in filtered list?

If the values to count are numeric as per your example then this formula will suffice, see attached

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A2,ROW(A2:A12)-ROW(A2),0)),A2:A12),A2:A12),1))

10. ## Re: How to count unique values in filtered list?

THIS WORX!! 1000THX

But unfortunately the real to be analysed data ar mixed type, text and numbers...
(And the original file has 15.000 lines, so it needs app 30 sec to be calculated)

Do you think there is also a solution?

THX /C

11. ## Re: How to count unique values in filtered list?

Hi,

I understand that the OP not only needs to know the number of unique items but also unique items when a list is filtered.
That implies knowing the row height.

A macro would do it but there's also the almost forgotten Excel4 functions, one of which is

Formula:
`Please Login or Register  to view this content.`

However whilst it can't be entered directly in a cell, it can be used in a defined range name.

The attached uses this function in a second helper column so that Marvin's 1st helper COUNTIF column can be extended to a COUNTIFS function. The file is no treated as a macro file hence the .xlsm extension.

12. ## Re: How to count unique values in filtered list?

Hi Richard,

It works properly with numbers.

Can it be changed, so that it works with text also?
There is no helper needed, what I think is an advantage for me...

THX /C

13. ## Re: How to count unique values in filtered list?

For any type of data canapone's solution should work for you - I added to my workbook - see cell in red

That will be slow though, so you might want to go with the helper cell solution

14. ## Re: How to count unique values in filtered list?

THANK YOU ALL!

The helpers ara also a perfct method, I'll remember, I Think I'll need them for another story...

THXTHXTHX Cayenne
Greets from Europe

15. ## Re: How to count unique values in filtered list?

Originally Posted by Cayenne
Hi Richard,

It works properly with numbers.

THX /C
Hi,

Sorry, I should have said I was referring to Marvin's solution which was giving unique values but only for unfiltered data - at least on my system. Hence why I added a 2nd helper column to his first.

ddl's of course works fine although as he implies array formulae may be slower with very large data sets.

It seems a shame and presumably an oversight that MS forgot to include a row height parameter in the CELL function. It contains a column width parameter but nothing for height so we can only imply that's a mistake, particularly given Excel4s 1st generation of Get.Cell functions which does include height.

Apologies for any confusion.

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1