Need help with formulas

[Sambasivam]

I have range of cells like

<TABLE style="WIDTH: 50pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=66 border=0><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15>DEF168967</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=66 height=30>INS168910, DEF169397</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=66 height=30>INS169330,
DEF168969</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=66 height=30>INS168908, DEF169396</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15>DEF169020</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15>DEF169398</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=66 height=30>INS169331, DEF168945</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=66 height=30> </TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" width=66 height=31>DEF169103</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=66 height=15> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=66 height=16>DEF168930</TD></TR></TBODY></TABLE>

How to extract the INS and DEF and count. I want it like

INS = 4
DEF = 9

Please help me if we can do it using Formulas or we need vba scripts. I'm new to this.

 

[djt76010]

If each cell can only have one "DEF" or "INS" in it then you can use a countif function.

=COUNTIF($A$1:$A$30,"*DEF*")

 

[HalfAce]

How about a couple formulas like:

=IF(COUNTIF(A:A, "*DEF*")>0,"DEF = " & COUNTIF(A:A, "*DEF*"),"")
=IF(COUNTIF(A:A, "*INS*")>0,"INS = " & COUNTIF(A:A, "*INS*"),"")

Of course you'll want to replace A:A in both formulas with the real column you want to test.

Hope it helps.

 

[Michael M]

Hi and welcome to the Board
In the adjacent column use

=Left(A1,3)

and copy down as far as required.
At the bottom of the data column use

=COUNTIF(A1:A1000,"DEF")' change ranges to suit
=COUNTIF(A1:A1000,"INS")

 

[Sambasivam]

Thanks to everyone. All the suggestions worked but I used HalfAce suggestions because it suited my need. Im happy to be here. Its wonderful. Wish you guys are here for beer.

 

[Sambasivam]

Im very thankful to all those who helped but there is another issue. Problem is I do not know all these formulas I have to come to you guys.

<TABLE style="WIDTH: 47pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=63 border=0><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><TBODY><TR style="HEIGHT: 33.75pt" height=45><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 33.75pt; BACKGROUND-COLOR: transparent" width=63 height=45>DEF168967, DEF169458, DEF169459</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=63 height=15> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=63 height=15> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=63 height=15> </TD></TR><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 23.25pt; BACKGROUND-COLOR: transparent" width=63 height=31>INS168910, DEF169397</TD></TR><TR style="HEIGHT: 27pt; mso-height-source: userset" height=36><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 27pt; BACKGROUND-COLOR: transparent" width=63 height=36>INS169330, DEF168969</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30> </TD></TR><TR style="HEIGHT: 29.25pt; mso-height-source: userset" height=39><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 29.25pt; BACKGROUND-COLOR: transparent" width=63 height=39>INS168908, DEF169396</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=63 height=15> </TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30>DEF169020</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30>DEF169398</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30> </TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30> </TD></TR><TR style="HEIGHT: 27.75pt; mso-height-source: userset" height=37><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 27.75pt; BACKGROUND-COLOR: transparent" width=63 height=37>INS169331, DEF168945</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30> </TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30> </TD></TR><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30> </TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=63 height=15> </TD></TR><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30> </TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30> </TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=63 height=30> </TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=63 height=15> </TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=63 height=15> </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=63 height=18>DEF169103</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=63 height=15> </TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 17.25pt; BACKGROUND-COLOR: transparent" width=63 height=23>DEF168930</TD></TR></TBODY></TABLE>

now when I changed the entry in first row with additional items I expected
INS = 4
DEF = 11
but it is not changing. It still shows INS = 4 and DEF = 9. I want it to adjust numbers when I insert either INS or DEF. I altered the formula and used this

=IF(COUNTIF(X14:X39, "*INS*")>0,COUNTIF(X14:X39, "*INS*"),"")
`=IF(COUNTIF(X14:X39, "*DEF*")>=0,COUNTIF(X14:X39, "*DEF*"),"")

Please help me it is urgent

 

[djt76010]

The problem is that multiple "DEF" in the first cell only counts as one instance of "*DEF*" in the countif function. Depending on how many times "DEF" can be in each cell you could use something like this.

=COUNTIF($A$1:$A$26,"*DEF*")+COUNTIF($A$1:$A$26,"*DEF*DEF*")+COUNTIF($A$1:$A$26,"*DEF*DEF*DEF*")

If more than 3 items can be in each cell then you would just have to add more countifs

 

[Sambasivam]

Thanks for that quick response. Isn't there any way to make it n entries? The entry of INS and DEF is not fixed for any cell. It may vary from 0 - n. How to handle the situation