Find number of customer did not purchase any of the product in cell A4:A8 before or equal to year 2015

Question

Hi all,

As mentioned in the screenshot above, I want to find number of customers who did not purchase any of the product in cell A4:A8 before or equal to year 2015.

In the screenshot above, we have 4 customers.

• For customer 101 - purchased Product A in 2015, so we will not include into the count.
• For customer 102 - did not purchase either Product A or Product B, so we will include this customer into the count.
• For customer 103 - purchased Product A in 2014, so we will not include in the count.
• For customer 104 - purchased Product B, but it is after Year 2017, so we will include this customer into the count.

So the expected output will be 2. I have challenge on how to use formula to loop through each and every customer as all of them having different number of rows in the table. Any help or advise will be greatly appreciated!

Edit

Answer 1

You may try:

=countunique(ifna(filter(C4:C,iserror(xmatch(C4:C,filter(C4:C,xmatch(D4:D,A4:A),E4:E<=G4))))))

• year is in G4 cell

Answer 2

Use QUERY() function like-

=QUERY(C3:E,"select count(C) where not D matches '" & TEXTJOIN("|",1,A2:A) & "' and E <=2015")