Count Unique values in a filtered Column

[webharvest]

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

 

[jim may]

Perhaps this will help...

Excel Workbook
	A	B	C
1	Bob		4
2	Ted
3	Carol
4	Alice
5	Ted
6	Bob
7	Carol
8	Ted
9	Carol
10	Ted
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[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

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

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

 

[webharvest]

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

 

[Aladin Akyurek]

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:

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

 

[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.......

 

[Aladin Akyurek]

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

 

[T. Valko]

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.

 

[TPortsmouth]

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.

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.