Combined IF formula - please help))

[Ann_Isauella]

Hi everyone,

I need to create a formula, which could indicate the merchant's status based on the current month's (Feb 23) revenue.
Merchants should have the following statuses:
Repeating 5k Merchant: Generated at least $5,000 USD in the current month and at least $5,000 USD in the previous month.
Lost 5k Merchant: Generated at least $5,000 USD in the previous month but did not generate any revenue during the current month.
Recovered 5k Merchant: Generated at least once $5,000 USD within the last 12 months before the current month, was in lost status last month, and generated $5,000 USD again in the current month.
New 5k Merchant: Generated $5,000 USD for the first time.

Below will find printscreen from excel with data:

 

[awoohaw]

something like this:

mr excel questions 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
10
11	Merchant		2022-02-01	2022-03-01	2022-04-01	2022-05-01	2022-06-01	2022-07-01	2022-08-01	2022-09-01	2022-10-01	2022-11-01	2022-12-01	2023-01-01	2023-02-01
12		Repeating	4,913	4,265	4,258	4,683	4,285	4,240	3,977	4,838	4,351	4,455	4,883	5,000	5,000
13		Lost	4,078	4,645	4,630	4,767	4,513	4,444	4,620	4,662	4,723	4,214	4,322	5,000	3,500
14		Recovered	4,030	4,417	4,371	4,879	4,030	3,916	4,174	4,487	4,832	4,250	5,000	4,900	5,000
15		New	4,539	4,209	4,424	4,512	4,219	4,379	3,966	4,920	3,986	4,559	4,933	4,752	5,000
16		No 5000 Ever	4,899	4,644	4,126	3,917	4,542	4,870	3,902	3,987	4,114	4,412	4,955	3,218	3,987
17
Sheet10

Cell Formulas
Range	Formula
C11:N11	C11	=EDATE(D11,-1)
O11	O11	=DATE(2023,2,1)
B12:B16	B12	=IF(SUM(--(N12:O12>=5000))=2,"Repeating", IF((--(N12>=5000))*(--(O12<5000))=1,"Lost", IF((--(M12>=5000))*(--(N12<5000))*(--(O12>=5000))=1,"Recovered", IF(SUM((--(C12:N12<5000))*(--(O12>=5000)))=12,"New","No 5000 Ever"))))

 

[awoohaw]

Can you clarify why you have to look back 12 months for this criteria:

Generated at least once $5,000 USD within the last 12 months before the current month, was in lost status last month, and generated $5,000 USD again in the current month.

If they were in lost status the prior month, then that means the 2nd month back had +5000, so checking the 10 months prior to that is unnecessary.