# Thread: Count Unique values in a filtered Column Thanks: 0 Likes: 0

1. ## Count Unique values in a filtered Column

I have people's names in Cells A9 to A42 and I want to count the number of unique people in the list so I can multiply the number of unique people by 40 and give me the total # of hours I have available in a week given a 40 hour work week.

I came up with the following formula which works great except the name column is filtered so when I select a filter value I get the same number no matter what the filter is set to.

=SUM(IF(FREQUENCY(IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""), IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""))>0,1,0))

So I tried the following:
=SUBTOTAL(9,(IF(FREQUENCY(IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""), IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""))>0,1,0)))

This doesn't work because I don't think the subtotal function works with an array (not sure about that though).

I also tried something like this
=SUM(IF(FREQUENCY(IF(LEN(A9:A42)>0,(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),0)),MATCH(A9:A42,A9:A42,0),)),""),(IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""))),1)) and it seemed to work but if the filter value doesn't have the first person in my rows as part of the selection it always comes back with a number 1 higher than it should.

I'm stumped. Is there a way to change the array to a list so the first subtotal function I posted will work or is there a better way altogether to accomplish what I want?

Thank you

2. ## Re: Count Unique values in a filtered Column

Perhaps this will help...

Sheet1
ABC
1Bob4
2Ted
3Carol
4Alice
5Ted
6Bob
7Carol
8Ted
9Carol
10Ted
Excel 2007

Array Formulas
CellFormula
C1=SUM(1/COUNTIF(A1:A10,A1:A10))
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

3. ## Re: Count Unique values in a filtered Column

Thank you but this won't work for a couple of reasons. The column is filtered so I don't want the unique count to add up names that are not selected in the filter. The sum feature adds the values whether they are showing up under the filter or not.

The second reason it won't work is I need to make sure it ignores blanks fields. The formula you suggested will return a divide by 0 error is blanks exist. I can get around that with some if statements but It still won't return the right value with the filter active.

Thanks anyway

4. ## Re: Count Unique values in a filtered Column

Control+shift+enter, not just enter:
Code:
```=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),,1)),
IF(A9:A42<>"",MATCH("~"&A9:A42,A9:A42&"",0))),ROW(A9:A42)-ROW(A9)+1),1))```

5. ## Re: Count Unique values in a filtered Column

Man. That works like a champ! What is the significants of the MATCH function with the "~" in it. I want to try to understand this more so I can do more than just copy this formula and have it work

6. ## Re: Count Unique values in a filtered Column

Originally Posted by webharvest
Man. That works like a champ!
Great. Thanks for providing feedback.

What is the significants of the MATCH function with the "~" in it. I want to try to understand this more so I can do more than just copy this formula and have it work
It prevents the side-effects of the special chars like *, <, etc. which can occur around the entries. If such is guaranteed not to occur, the following will do just as well:

Code:
```=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),,1)),
IF(A9:A42<>"",MATCH(A9:A42,A9:A42,0))),ROW(A9:A42)-ROW(A9)+1),1))```

7. ## Re: Count Unique values in a filtered Column

Ahhhh!!!

One last question. I used the formula evaluated in excel because I wasn't getting how the offset function was working in the subtotal. Interestingly enough when I click through the evaluator it shows the OFFSET(A9,ROW(A9:A46)-ROW(A9),,1) in the subtotal as equally a bunch a bunch of #VALUE! (ie {#VALUE!;#VALUE!........} but when I step through the subtotal function it comes back with 0s and 1s like I would think it would.

Why is that?

Thanks for helping me understand.......

8. ## Re: Count Unique values in a filtered Column

Originally Posted by webharvest
Ahhhh!!!

One last question. I used the formula evaluated in excel because I wasn't getting how the offset function was working in the subtotal. Interestingly enough when I click through the evaluator it shows the OFFSET(A9,ROW(A9:A46)-ROW(A9),,1) in the subtotal as equally a bunch a bunch of #VALUE! (ie {#VALUE!;#VALUE!........} but when I step through the subtotal function it comes back with 0s and 1s like I would think it would.

Why is that?

Thanks for helping me understand.......
See the explanation of Laurent Longre who devised the Subtotal/Offset expression:

http://j-walk.com/ss/excel/eee/eee001.txt

9. ## Re: Count Unique values in a filtered Column

Originally Posted by webharvest
Thank you but this won't work for a couple of reasons. The column is filtered so I don't want the unique count to add up names that are not selected in the filter. The sum feature adds the values whether they are showing up under the filter or not.

The second reason it won't work is I need to make sure it ignores blanks fields. The formula you suggested will return a divide by 0 error is blanks exist. I can get around that with some if statements but It still won't return the right value with the filter active.

Thanks anyway
If the "blank" cells are EMPTY cells...

Array entered**

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),0,1)),MATCH(A9:A42,A9:A42,0)),ROW(A9:A42)-ROW(A9)+1)>0,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

10. ## Re: Count Unique values in a filtered Column

Hello,

I am also trying to do the same thing and navigate to this topic. What a brilliant idea!!

However, for the blank cell calculation, I have further question on this. I also need to exclude those "Blank" cells, however, those "Blank" cells are not actually "Empty", the range is a calculated field in which I used IF formula like below:

=IF(A1>10, "Count", "")

Is there a way to further exclude these "Null" value?

Thanks.

Originally Posted by T. Valko
If the "blank" cells are EMPTY cells...

Array entered**

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),0,1)),MATCH(A9:A42,A9:A42,0)),ROW(A9:A42)-ROW(A9)+1)>0,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.