How to count unique values in filtered list?

**Cayenne** · 10-23-2014, 08:40 AM

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

**Richard Buttrey** · 10-23-2014, 08:46 AM

Hi,

Have you thought of using

Formula:

Please Login or Register  to view this content.

**Cayenne** · 10-23-2014, 09:10 AM

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

**romperstomper** · 10-23-2014, 09:30 AM

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

**canapone** · 10-23-2014, 10:47 AM

Hi All,

Please Login or Register  to view this content.

to be confirmed withn control+shift+enter

Ich hoffe, es ist ein wenig Hilfe

**Cayenne** · 10-23-2014, 10:50 AM

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

**MarvinP** · 10-23-2014, 10:56 AM

Hi,

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

**Cayenne** · 10-23-2014, 11:05 AM

Hi Canapone!

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

THXTHX C.

**daddylonglegs** · 10-23-2014, 11:12 AM

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

**Cayenne** · 10-23-2014, 11:30 AM

Hi daddylonglegs!

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

**Richard Buttrey** · 10-23-2014, 11:32 AM

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.

**Cayenne** · 10-23-2014, 11:50 AM

Hi Richard,

thanks a lot, what do you mean about daddylongleg's solution?
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

**daddylonglegs** · 10-23-2014, 11:55 AM

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

**Cayenne** · 10-23-2014, 12:10 PM

THANK YOU ALL!

I will choose the latest version of daddylonglegs, this works also with text or mixed data and it is quickly added!

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

THXTHXTHX Cayenne
Greets from Europe

**Richard Buttrey** · 10-23-2014, 04:58 PM

Originally Posted by Cayenne

Hi Richard,

thanks a lot, what do you mean about daddylongleg's solution?
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.

How to count unique values in filtered list?

LinkBack

Thread Tools

Rate This Thread

Display

How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Re: How to count unique values in filtered list?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count number of unique names in a filtered list (non array)

Count unique, visible values in a filtered column

count unique values in filtered column

Count Unique Values in a Filtered Column

Count Unique Values In A Filtered Row with Duplicates

Bookmarks

Bookmarks

Posting Permissions