Partial Criteria Returning Count of Unique Values

[bananney]

I am trying to get a formula that will return the following result. If column B shows "Out-of-Town" any where in the cell, then I want to count the number of unique employees that are out-of-town at any time during that week. For this particular week there should be 2. The problem I have is that the words "Out-of-Town" are only a portion of the cell and using the wildcard asterick doesn't give the correct answer.

[pre]

Employee	Location
135	Out-of-Town Monday
201	At Work
344	At Work
135	Out-of-Town Friday
344	Out-of-Town Wednesday

[/pre]

 

[NARAYANK991]

Hi ,


I think the problem is more one of defining the start and end of any week ; do we take it that since in your example given above , Wednesday comes after a Friday , it is in the next week ?


Instead of the weekday , if the actual date can be used , the formula may be more robust.


Narayan

 

[bananney]

I want it to simply work for this week. Assuming that this is one week and I am not using dates (only days of the week), how can it work?

 

[NARAYANK991]

Hi ,


Can you try this ?


=SUM(IF(LEFT(Locations,LEN("Out-of-Town"))="Out-of-Town",1/COUNTIFS(Locations,"Out-of-Town*",Employee_Codes,Employee_Codes)))


Locations is the range in column B ; Employee_Codes is the range in column A.


The formula is to be entered as an array formula , using CTRL SHIFT ENTER.


Narayan