Ifs & and

[TG2812]

Hello. First of all, thank you very much for taking time to review the below case.

So I have a data set and I'm attempting to build a formula with IFS and AND to determine the following;

• New Customers: customers who have never purchased any of the selected products.
• Returning Customers: customers who bought at least one of the selected products in the past (no matter when).
• Lost Customers: customers who bought any of the selected products in the past but did not buy in the last period (defined as a number of days/months/years).
• Recovered Customers: customers who were considered lost before the defined period (like Lost Customers) but made a purchase in the period.

I build a pivot table counting all purchases made by customerID (R7:AB7) and by date ($R$4:$AB$4). Goal is to determine which category of customer each ID (row) belongs to.

The formula that reads in the pivot table is:

=IFS(AND(SUMIF($R$4:$AB$4,$AG$6,R7:AB7)>0,SUMIF($R$4:$AB$4,"<"&$AG$6,R7:AB7)=0),"New Client",AND(SUMIF($R$4:$AB$4,$AG$6,R7:AB7)=0,SUMIF($R$4:$AB$4,$AG$6-1,R7:AB7)>0),"Returning Client",AND(SUMIF($R$4:$AB$4,$AG$6,R7:AB7)>0,SUMIF($R$4:$AB$4,$AG$6-1,R7:AB7)>0),"Lost Client",TRUE,"")

Problems;

• I do not find the solution for the recovered customer
• The above formula does not results I’m looking for (see above definitions).

Any help would be greatly appreciated ?