Countif Unique Values

[Velven]

Hello !

I am trying to make a counting for unique value on the same date.
I've seen several formulas here and it's too complex for me to understand .

Staff record ( Sheet 1 )
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Staff[/TD]
[TD]Dep Quantity[/TD]
[TD]Wa Quantity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12/1/2017[/TD]
[TD]Alex[/TD]
[TD]Expected value
1[/TD]
[TD]Expected Value
1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/1/2017[/TD]
[TD]John[/TD]
[TD]Expected Value
2[/TD]
[TD]Expected Value
0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12/2/2017[/TD]
[TD]Alex[/TD]
[TD]Expected Value
1[/TD]
[TD]Expected Value 0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12/2/2017[/TD]
[TD]John[/TD]
[TD]Expected Value 0[/TD]
[TD]Expected Value 1[/TD]
[/TR]
</tbody>[/TABLE]

Customers Record ( Sheet 2 )
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Customers[/TD]
[TD]Dep or Wa[/TD]
[TD]Staff[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12/1/2017[/TD]
[TD]Cust A[/TD]
[TD]Dep[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/1/2017[/TD]
[TD]Cust A[/TD]
[TD]Wa[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12/1/2017[/TD]
[TD]Cust A[/TD]
[TD]Dep[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12/1/2017[/TD]
[TD]Cust B[/TD]
[TD]Dep[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]12/1/2017[/TD]
[TD]Cust C[/TD]
[TD]Dep[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]12/2/2017[/TD]
[TD]Cust C[/TD]
[TD]Wa[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]12/2/2017[/TD]
[TD]Cust D[/TD]
[TD]Dep[/TD]
[TD]Alex[/TD]
[/TR]
</tbody>[/TABLE]

So what I'm trying to do is under C2:E5 must be filled in with the values shown on Sheet 2.

C2 must check date on both sheet if matches , check staff on both sheets , Check if it's a Dep or Wa. Check if it's a Unique Customer for the current date.

Dep and Wa must be separate. Meaning Unique Wa count and Unique Dep count , only unique for the same date.


If anybody even understand what I'm trying to do . I suck at explaining. But it's too complex for me to make a formula.

 

[AhoyNC]

Try:
These are array formulas and must be entered with CTRL-SHIFT-ENTER.

Drag formulas down column as needed.

Excel Workbook
	A	B	C	D
1	Date	Staff	Dep Quantity	Wa Quantity
2	12/1/2017	Alex	1	1
3	12/1/2017	John	2	0
4	12/2/2017	Alex	1	0
5	12/2/2017	John	0	1
Sheet1

Excel Workbook
	A	B	C	D
1	Date	Customers	Dep or Wa	Staff
2	12/1/2017	Cust A	Dep	Alex
3	12/1/2017	Cust A	Wa	Alex
4	12/1/2017	Cust A	Dep	Alex
5	12/1/2017	Cust B	Dep	John
6	12/1/2017	Cust C	Dep	John
7	12/2/2017	Cust C	Wa	John
8	12/2/2017	Cust D	Dep	Alex
Sheet2