Categorizing and Timing Formula

idontexcelinexcel,

We are light on data. Without a subscriber's history (by dates) I don't see a way to discriminate New from Continuing or Renewal.

hi there. i don't understand why row 11 is "Renewal". your criteria says "3. If customer already existed and subscribed again but after the prior subscription end date……"Renewal".

Glen Doe subscribed in 1-Dec-16 under row 11. his last subscription ended in 1-Nov-16. that is just 30 days and within 90 days right? so shouldn't it be "Continuing Customer"?

if what i explained is correct, your formula could be:

Formula:

Please Login or Register  to view this content.

adding a column with the date the subscriber first appeared helps. assuming that is available in column E, formula would look something like:
=IF(COUNTIF($A$2:A2,A2)=1,"New Customer",IF((B2-E2)<=90,"Continuing Customer","Renewal"))

e2=IF(MATCH(A2,A:A,0)=ROW(),"New Customer",IF(B2>LOOKUP(2,1/(A$1:A1=A2),C$1:C1),"Renewal","Continuing Customer"))
Try this and copy towards down

Originally Posted by idontexcelinexcel

Hello Flame, I see your point.
I'm thinking of adding a column with the date the subscriber first appeared.
Would that help?
Any suggestions?

It would help me. But apparently others see something I do not. Check their work. It might save you some trouble.

ARRAY formula in E2, Then drag down

=IF(COUNTIF($A$2:$A2,$A2)=1,"New Customer",IF($B2>MAX(IFERROR(($A$1:$A1=$A2)*($C$1:$C1),0)),"Renewal",IF($B2>=MAX(IFERROR(($A$1:$A1=$A2)*($C$1:$C1),0))-90,"Continuing Customer","")))

Formula in F21, Then drag across

=SUMPRODUCT(($E$2:$E$11=$E21)*($B$2:$B$11<=F$13)*($C$2:$C$11>=F$13)*($D$2:$D$11))