# Count Unique Values based on 2 other criteria

#### ineedhelp1

##### New Member
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?

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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:
Rich (BB code):
``````
=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))
``````

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

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

You are welcome.

Replies
9
Views
147
Replies
3
Views
236
Replies
6
Views
139
Replies
1
Views
352
Replies
4
Views
92

1,196,516
Messages
6,015,673
Members
441,915
Latest member
sm Hussaini

### 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.

### Which adblocker are you using?

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

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