TG2812

Board Regular
Joined
Apr 15, 2015
Messages
176
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:

Code:
=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 ?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,123,504
Messages
5,602,060
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top