IF statement across multiple customer records

[dancepants]

Hi all

I have a customer dataset where each row represents a customer's subscription. Each customer can have one or more subscriptions. I would like to categorize each customer, based on the type and status of their subscriptions, into three groups: "Cancelled", "One time", or "Recurring".
"Cancelled" would be a customer where ALL of their subscriptions are cancelled
"One time" would be a customer that only has a One-time subscription that is active
"Recurring" would be a customer that has at least one Active and recurring subscription

Here's an example of the dataset:

Customer ID	Subscription cancelled date	Recurring Frequency	One-time only?
1005	Active	One-time	One-time
1006	Cancelled	One-time	Recurring
1006	Active	Quarterly	Recurring
1007	Cancelled	Quarterly	Cancelled
1008	Active	Monthly	Recurring
1008	Cancelled	Quarterly	Recurring
1009	Cancelled	Monthly	Cancelled
1009	Cancelled	One-time	Cancelled

First 3 columns are in my dataset, I need to create the 4th with an excel formula. So my question is, what formula could I use that looks across ALL customer records, and assigns the correct category based on multiple conditions?

Hopefully that's clear. Thanks everyone in advance

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jtakw]

Hi,

Would this work for you:

Book3.xlsx
	A	B	C	D
1	Customer ID	Subscription cancelled date	Recurring Frequency	One-time only?
2	1005	Active	One-time	One-time
3	1006	Cancelled	One-time	Recurring
4	1006	Active	Quarterly	Recurring
5	1007	Cancelled	Quarterly	Cancelled
6	1008	Active	Monthly	Recurring
7	1008	Cancelled	Quarterly	Recurring
8	1009	Cancelled	Monthly	One-time
9	1009	Active	One-time	One-time
Sheet1051

Cell Formulas
Range	Formula
D2:D9	D2	=IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"Active",C$2:C$9,"<>One-time"),"Recurring",IF(COUNTIFS(A$2:A$9,A2,B$2:B$9,"Active",C$2:C$9,"One-time"),"One-time","Cancelled"))

 

[Peter_SSs]

Thanks for updating your profile.

Another option that you could try:

22 03 21.xlsm
	A	B	C	D
1	Customer ID	Subscription cancelled date	Recurring Frequency	One-time only?
2	1005	Active	One-time	One-time
3	1006	Cancelled	One-time	Recurring
4	1006	Active	Quarterly	Recurring
5	1007	Cancelled	Quarterly	Cancelled
6	1008	Active	Monthly	Recurring
7	1008	Cancelled	Quarterly	Recurring
8	1009	Cancelled	Monthly	Cancelled
9	1009	Cancelled	One-time	Cancelled
10	1006	Active	Monthly	Recurring
Subs

Cell Formulas
Range	Formula
D2:D10	D2	=LET(ot,"One-time",x,"Cancelled",f,FILTER(C$2:C$10,(A$2:A$10=A2)*(B$2:B$10<>x),NA()),IFNA(IF(INDEX(FILTER(f,f<>ot,""),1)="",ot,"Recurring"),x))