finding unique values on filter

excel_beg

New Member
Joined
Jan 27, 2006
Messages
27
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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...

Code:
=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!
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top