How to count unique values with few criterias between two lists/ Count the number of duplicates with few criterias bwtween two lists

[jychuah]

For example, I have two list, each with three columns of criteria. From here I want to find the number of unique customer ID that bought orange on Wednesdays.


List 1

Customer ID (Column A)	Items bought (Column B)	Day (Column C)
1	Orange	Wednesday
2	Apple	Tuesday
5	Pear	Wednesday
5	Orange	Wednesday

<tbody>
</tbody>

List 2

Customer ID (Column E)	Items bought (Column F)	Day (Column G)
5	Orange	Wednesday
6	Orange	Friday
4	Pear	Thursday
2	Orange	Wednesday
3	Orange	Wednesday

<tbody>
</tbody>

I have tried the approach of minus off the duplicates by using the formula below to count the duplicates:
=SUM(IF(((F2:F6="Topup")*(G2:G6="Pass")),1/COUNTIFS(E2:E6,A2:A5,B2:B5,"Topup",C2:C5,"Pass"),0))
However, it does not work well and it gives me DIV/0! error.

Can anyone please help me with this approach or suggest me a new approach?


Many thanks,
jy