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

[TABLE="class: k-table, width: 844"]
<tbody>[TR]
[TD]Customer ID (Column A)[/TD]
[TD]Items bought (Column B)[/TD]
[TD]Day (Column C)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Orange[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pear[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Orange[/TD]
[TD]Wednesday[/TD]
[/TR]
</tbody>[/TABLE]

List 2

[TABLE="class: k-table, width: 844"]
<tbody>[TR]
[TD]Customer ID (Column E)[/TD]
[TD]Items bought (Column F)[/TD]
[TD]Day (Column G)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Orange[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Orange[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pear[/TD]
[TD]Thursday[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Orange[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Orange [/TD]
[TD]Wednesday[/TD]
[/TR]
</tbody>[/TABLE]
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