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

 

[Aladin Akyurek]

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.

Control+shift+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))

 

[TPortsmouth]

Thank you so much Aladin.

It works perfectly!!!

 

[Aladin Akyurek]

Thank you so much Aladin.

It works perfectly!!!

You are welcome.

 

[TPortsmouth]

Hello Aladin,

I have further question on this array formula.

You've help me how to deal with "Blank" column, by below formula:

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B3,ROW(B3:B100)-ROW(B3),,1)),IF(B3:B100 <>"",MATCH(B3:B100,B3:B100,0))),ROW(B3:B100)-ROW(B3)+1),1))}

Yes, it's working fine, thank you.

However, I have a situation with multiple record set. The setup is like below:
1. Each record would have 3 rows (Due to special purpose). That is, column A is a merged cell while other records would consists 3 columns.
2. For the actual value store at Column B.
3. For example, the actual value for record 1 stored at B3 while B4 and B5 would store the same value as B3 using a simple formula =B3.

Now my problem is: Those value with formula =B3, =B6, =B9, etc... would still be treated as an unique record, how do I exclude this also?

Thanks again.

TPortsmouth

 

[Aladin Akyurek]

@TPortsmouth

What is the unique count for this exhibit?

 

[TPortsmouth]

I just try to count the unique value in column B. I've eventually figure out a solution, simple change the <>"" to <>0 would do the job.

Thanks for your original idea and your inspiration, cheers

@TPortsmouth

What is the unique count for this exhibit?

 

[Aladin Akyurek]

I just try to count the unique value in column B. I've eventually figure out a solution, simple change the <>"" to <>0 would do the job.

Thanks for your original idea and your inspiration, cheers

I don't understand what <>0 has to do with =B3, =B6, and =B9?

 

[TPortsmouth]

This is due to when B3 is blank, then the formula =B3 would show 0.
That's why not equal 0 would work.

I don't understand what <>0 has to do with =B3, =B6, and =B9?

 

[Aladin Akyurek]

This is due to when B3 is blank, then the formula =B3 would show 0.
That's why not equal 0 would work.

I'm not clear at all.

In

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B3,ROW(B3:B100)-ROW(B3),,1)),IF(B3:B100 <>"",MATCH(B3:B100,B3:B100,0))),ROW(B3:B100)-ROW(B3)+1),1))}

we already have a test for non-blank: B3:B100 <>"" which says not a formula blank or empty!

Is B3:B100 in your case a numeric range?

 

[TPortsmouth]

If you build the same data set by thread#14, then you will see how it works.

I'm not clear at all.

In

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B3,ROW(B3:B100)-ROW(B3),,1)),IF(B3:B100 <>"",MATCH(B3:B100,B3:B100,0))),ROW(B3:B100)-ROW(B3)+1),1))}

we already have a test for non-blank: B3:B100 <>"" which says not a formula blank or empty!

Is B3:B100 in your case a numeric range?