Need Unique count of values based on two columns criterias

[krishnabsharma]

Hi,

I have a set of data records in excel sheet. I want to display unique count of employees per department.

E.g. - In Dept1, if there are 3 entries for Resoruce1 then i want to show only 1 and not 3.

Attached herewith is the sample data.

Looking forward for help. Thanks.

Regards,
Krishna

 

[Debraj]

Hi Krishna,

Try this..

=SUM(IF(($B$2:$B$26=$F$2),1/COUNTIFS($C$2:$C$26,$C$2:$C$26,$B$2:$B$26,"Resource1")))

Please confirm the formula by pressing Ctrl + Shift + Enter, not just Enter.

 

[krishnabsharma]

Hi Deb,
Thanks for your efforts. Formula seems to be correct but it is having one dependency or a manual intervention -
In formula you kept resource name in CELL F2 which will not be the case. Is there any way we can map that parameter directly to the basedata resoruce name column and fetch the resource name from there itself?

 

[NARAYANK991]

Hi Sharma ,

Can you check your file ?

Narayan

 

[krishnabsharma]

Sorry but i want the result at each department level and not at resource level.

E.g. In Dept1, Resource1 is appearing 3 times, Resource2 is appearing 5 times,Resource3 is appearing 1 time; then the resulting cell should show me the unique count of Dept1/Resource wise as 3 only [(Resource1 -1) +(Resource2 -1)+(Resource3 -1)]

Hope i am able to clear out my requirement.

 

[NARAYANK991]

Hi ,

Your layout is deceptive ! The major level should always be the first column , the next level(s) should be placed after this.

What about the file now ?

Narayan

 

[krishnabsharma]

So sorry for that Narayan .

This is perfect sir . This is something what i was looking for. Now i can easily get unique count in summary sheet at each dept through PIVOT table. Thanks a ton !

One more help if there is any way - How about if i have to display this dept level summary somewhere else in sheet through some formula and not pivot table.
I mean to say - if i have only 2 depts and in summary sheet i have to show the unique count for these depts summary, how can i do that?

 

[NARAYANK991]

Hi Sharma ,

See your file now.

Narayan

 

[krishnabsharma]

That's awesome!

Grt, thnx for your help...