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 )

	A	B	C	D
1	Date	Staff	Dep Quantity	Wa Quantity
2	12/1/2017	Alex	Expected value 1	Expected Value 1
3	12/1/2017	John	Expected Value 2	Expected Value 0
4	12/2/2017	Alex	Expected Value 1	Expected Value 0
5	12/2/2017	John	Expected Value 0	Expected Value 1

<tbody>
</tbody>

Customers Record ( Sheet 2 )

	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

<tbody>
</tbody>

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