finding unique values on filter

[excel_beg]

hello experts,
I have a huge report and have applied filters to it. One of the fields is week number, and corresponding user names in the other column. What I need is number of unique ids from the user name column depending on which week I am looking at. So if I pick those fields that have value of week 1,2,3 or 4, then I want to know the number of unique ids.
How can I achieve this?

Thanks in avance!

 

[Domenic]

Assuming that A2:A100 contains the week number, B2:B100 contains the corresponding user name, and G2 contains the week number of interest, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2),0,1)),IF($A$2:$A$100=G2,IF($B$2:$B$100<>"",MATCH("~"&$B$2:$B$100,$B$2:$B$100&"",0)))),ROW($B$2:$B$100)-ROW($B$2)+1),1))

Hope this helps!

 

[excel_beg]

Yes it worked. Thanks for your quick response.
Is there a way to mark my question as answered? Just want to ensure I've marked it appropriately.

 

[Domenic]

Yes it worked. Thanks for your quick response.

You're very welcome!

Is there a way to mark my question as answered? Just want to ensure I've marked it appropriately.

Thankfully, there's no way to mark questions answered. It would only discourage those who may have a more efficient alternative to reply.