Count Unique Values based on 2 other criteria

[ineedhelp1]

Hi,

I have my data set out in the following format

01/01/2015	licence plate 1	DAY SHIFT
01/01/2015	licence plate 1	NIGHT SHIFT
01/01/2015	licence plate 2	NIGHT SHIFT
01/01/2015	licence plate 2	DAY SHIFT
02/01/2015	licence plate 1	NIGHT SHIFT

<tbody>
</tbody>

I need to count the unique values based on a single date and then to count if it meets the criteria of NIGHT SHIFT only.

So the result I would expect for the 01/01/2015 would be 2 vehicle licence plates did a Night shift on this date.

I have achieved a full count of unique values by using the array formula:

{=SUM(IF(FREQUENCY(IF(LEN(O7:O12)>0,MATCH(O7:O12,O7:O12,0),""), IF(LEN(O7:O12)>0,MATCH(O7:O12,O7:O12,0),""))>0,1))}

However, I'm really struggling to get this to only count if it says the date and the NIGHT SHIFT criteria.

Is any one able to point me in the right direction?

Thanks in advance!

 

[Aladin Akyurek]

E2: 01/01/2015
F2: NIGHT SHIFT

A2:C6 houses the sample you posted.

G2, control+shift+enter, not just enter, and copy down for any additional criteria records:

=SUM(IF(FREQUENCY(IF($A$2:$A$6=$E2,IF($B$2:$B$6<>"",
    IF($C$2:$C$6=$F2,MATCH($B$2:$B$6,$B$2:$B$6,0)))),
    ROW($B$2:$B$6)-ROW($B$2)+1),1))

 

[ineedhelp1]

Thank you so much - No chance I would have figured this out!

 

[Aladin Akyurek]

Thank you so much - No chance I would have figured this out!

You are welcome.