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]

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

No thanks. I don't have time to re-type from an image.

 

[TPortsmouth]

The general idea is, as I have some "Blank" columns and some other cells which refer to these "Blank" columns, thus "0" will be shown. So I've tried to use <>0 and your formula would also ignore those value. And it works!!!

Anyway, you helped me a lot by your original array formula, big thanks.

No thanks. I don't have time to re-type from an image.

 

[manu_n87]

I'm having a problem with the formula:

"=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(D2,ROW(D2:D22)-ROW(D2),,1)), IF(D2:D22<>"",MATCH("~"&D2:D22,D2:D22&"",0))),ROW(D2:D22)-ROW(D2)+1),1))"

My data is as shown below. The result I would like to have is the count of unique names in the Counselor when I filter the Recruiter field.
I tried to use the formula but it only gives me a "#VALUE".

I really need your help on this.

COUNSELOR(Filtered)	RECRUITER (Filtered)
CONNIE	FLORESITA
JANEL MARISE	ROE
BERNARDO	NORMA
REBECCA	GRACE
FRANCES ANN	ARVENTO
BRUCE GLENN	RODRIGO
HARRIB VIEN	ROMA
BRUCE GLENN	RODRIGO
HARRIB VIEN	ROMA
ERWIN	LOIDA
BRUCE GLENN	RODRIGO
CONCEPCION	WIL
JULIA	FE
JUDITH	JOCEL
HARRIB VIEN	ROMA
DANA ROANNE	ROMA
LETICIA	ANABEL
LETICIA	ANABEL
JOHN	ILAN
JOHN	ILAN
ADELAI	RUBY
ROSELYN	RUSS
ROSELYN	RUSS
HARRIB VIEN	ROMA
HARRIB VIEN	ROMA
DANI	JOCELYN
WENNIE	JOSEL
DANA ROANNE	ROMA
JOIDA	ELDA
JEMUELL	ELDA
JARA	ROMA
JARA	ROMA
NESTOR	JENN
ORHEL	ROMA
JOIDA	ELDA
LEON	ROMA

<tbody>
</tbody>

 

[Aladin Akyurek]

@ manu_n87

You need to confirm the formula with control+shift+enter, not just enter, that is, press down the control and the shift keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[manu_n87]

OMG.. Thank you very much Aladin. I was stuck on that formula for almost 4 hours. Great help.

@ manu_n87

You need to confirm the formula with control+shift+enter, not just enter, that is, press down the control and the shift keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[Aladin Akyurek]

OMG.. Thank you very much Aladin. I was stuck on that formula for almost 4 hours. Great help.

You are welcome.

 

[Marcelo Branco]

Try

Array formula
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(D2:D37,ROW(D2:D37)-ROW(D2),0,1)),IF(D2:D37<>"",MATCH("~"&D2:D37,D2:D37&"",0))),ROW(D2:D37)-ROW(D2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

EDIT:
Aladin has already provided a solution but you need to adjust the ranges - according your post the data are in rows 2:37; headers in row 1


M.

 

[Lokeshkumar]

Hi Aladin,

Your formula works perfectly for multiple blank rows as well.. Could you please let me know how to get the sum of records instead of count of records.

I have an urgent need for this.

Appreciate your efforts.

Thank you