0

enter image description here

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

enter image description here

weizer
  • 1,009
  • 3
  • 16
  • 39

2 Answers2

0

Use QUERY() function like-

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

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Hi Harun, thanks for the solution. However, when I change cell E10 to `2014`, the expected output should be 1, which is coming from customer ID `102`. But the query that you give still show count of 2, any idea on why is it so? I edited my question with a screenshot modified cell E10 – weizer Mar 10 '23 at 05:20
  • Because `D9` still `Product A`. – Harun24hr Mar 10 '23 at 06:27
  • No, because customer `101` ,`103`,`104` will not be included in the count because all these 3 customers having product A or B and purchase before or equal to year 2015. Only customer `102` will be counted. So the final output should be `1` only. – weizer Mar 10 '23 at 09:16
0

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

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19