Counting unique values (Employee's) using 3 criteria: Month, Manager, Project

[gizmo]

I have a table that includes Empl name, Empl ID, Month, Day, Manager, Hours, and Project. Each employee (EE) bills hours to a certain project each week. Being that the data is collected by week, the EE is represented multiple times per month. I need to count the unique number of EE's using the follow criteria: Month, Manager, Project. I use Excel 2010 and my data is stored in a "table", so when referenced in formulas, the table name is used so the data remain dynamic.


For the life of me, i cant figure out how to count the individual EE's that submitted Hours by the Month, Manager, and Project!


For EX all of the below have the criteria: Manager = Rick, Month = JUNE 2012 and Project = XYZ:

Sue, Bob, Joe bill 120 hours in week 1

Sue, Kevin, Sam bill 120 hrs in week 2

Diane, Pete, Sam bill 120 hrs in week 3

Pete, Paul, Sam bill 120 hrs in week 4


My count unique EE's for the month of June, w/ Manager Rick, and Project XYZ should be 8 (Sue, Bob, Joe, Kevin, Sam, Diane, Pete, Paul).


HELP??? PLEASE!!!

 

[Sajan]

Hi @gizmo,

Assuming that your data is setup something like below:

[pre]

A       B       C       D       E
Month	Manager	EE	Project	Week
June	Rick	Sue	XYZ	1
June	Rick	Joe	XYZ	2
June	Rick	Joe	XYZ	4
June	Rick	Bob	XYZ	5
June	Rick	Bob	XYZ	1
June	Smith	Rob	XYZ	3
June	Rick	Ken	XYZ	2
June	Smith	Sue	ABC	1

[/pre]
You could try the following formula, entered with Ctrl+Shift+Enter to get the unique count of employees for month of June, Manager Rick, Project XYZ:

=SUM((IF((A2:A9="June")*(B2:B9="Rick")*(C2:C9<>"")*(D2:D9="XYZ"),1/COUNTIFS(C2:C9,C2:C9,A2:A9, "June", B2:B9,"Rick",C2:C9,"<>",D2:D9,"XYZ"))))


(Adjust your data ranges as needed.)


Cheers,

Sajan.

 

[gizmo]

Thank you Sajan. Your example above works, but isnt working for me. Do you have an email address i can send my example to by chance?

 

[Debraj]

hi Gizmo,


Welcome to the forum..

Just to inform you, I personally send Sajan, a huge list of mails, regrading his

* Lottery Prize of 1,500,000 GBP,

* Free I-Pod by signing to my website.

* Forward my mail, and you will get $20 per forward..


But this man never replies..

So, I suggest you, Please don't choose this route to send him your example..

If you want him to see your example.. Just post it in any of the below reference website.. and paste the link here..


http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Deb

 

[SirJB7]

@gizmo


Hi!


Additionally to what Debraj Roy wrote, I'd suggest you that in the case you want to comunicate by mail with someone you could post your own mail, ask to get back and wait for the reply. If that person agrees to answer you then you can go on, but consider that as an exceptional way: the usual and normal is to upload a file so as everyone who read your post can get all the related info.


So I'd recommend you to consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you.


Perhaps you'd want to read too the three green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on). Give a look at the second one uploading guidelines.


Regards!


PS: This is a personal comment but I think is more polite to offer you email than asking for other people's ones. Check this, please:

http://chandoo.org/forums/topic/locking-the-cell-automaticall-once-the-date-passed#post-21903