Excel If condition

New Contributor

Hi Community
I am looking to replicate the same scenerio as per "D" column in Column "B'.
I am unable to understand the pattern there.
Just looking if anyone can help me what condition someone follow to get the category 1,2,3..  so that i could get the same result in column "B"

   Expected output for Problem 2
Instrument NameIssuer Category Category
A BC   
A BC AUG 21 210C  Category 1
A BC OCT 21 150C  Category 1
A BC OCT 21 160P  Category 1
D ECategory 2  
D E AUG 21 10C  Category 2
D E AUG 21 7.5C  Category 2
D E NOV 21 10C  Category 2
MCategory 3  
M DEC 21 2.5C  Category 3
ST DEC 21 72.51C  Category 4
ST DEC 21 77.51CCategory 4  
ST DEC 21 84.99C  Category 4
DGCategory 5  
DG JAN 23 22.5P  Category 5
RG AB  Category 6
RG AB JAN 23 20CCategory 6  
RG AB JAN 23 30C  Category 6
T RE JAN 23 50PCategory 7  
T RE MAR 22 60C  Category 7
RS DEC 21 72.5C  Category 8
RS JAN 22 50P  Category 8
RS JAN 22 57.5C  Category 8
RS OCT 21 65PCategory 8  
RS OCT 21 70C  Category 8
BR DEC 21 19C  Category 9
BR JAN 22 19CCategory 9  
BR JAN 22 20C  Category 9
BR MAY 22 19C  Category 9
BR NOV 21 18C  Category 9
FT JAN 22 77CCategory 10  
FT NOV 21 72.5C  Category 10
FT OCT 21 78.15C  Category 10
Y WEEKLY 10/01/21W 185C  Category 11
1 Reply

@meenu_verma 

In B3:

 

=SUM(E2,IF(LEFT(A3,IF(IFERROR(FIND(" ",A3,IFERROR(FIND(" ",A3),0)+1),LEN(A3)+1)-IFERROR(FIND(" ",A3),0)<=3,IFERROR(FIND(" ",A3,IFERROR(FIND(" ",A3),0)+1),LEN(A3)+1),IFERROR(FIND(" ",A3),0))-1)=LEFT(A2,IF(IFERROR(FIND(" ",A2,IFERROR(FIND(" ",A2),0)+1),LEN(A2)+1)-IFERROR(FIND(" ",A2),0)<=3,IFERROR(FIND(" ",A2,IFERROR(FIND(" ",A2),0)+1),LEN(A2)+1),IFERROR(FIND(" ",A2),0))-1),0,1))

 

Apply the following custom number format to B3:

 

"Category "0

 

Then fill down.