coutnig the occurence of string in text and remove redundancy in data

[anu08]

22 abc ntc -jkl hft ac *abc*

50 ser ghi-bvn kun ntc * hft * 1

24 lon nik-fty nik dp * ntc * 1

29 abc uvw-hft tyu * nik * 0


i am trying to count how many times abc appears with hft and ntc. i am using =COUNTIFS(B1:B5,D3,B1:B5,E2) formula where d3 is * hft * and e2 is * abc *. In the same manner for next row i m using countifs function. but with *abc*, * ntc * should return 0 not 1. if it comes in last (in second row) then it is denoting location which i want to match with *abc*. in first row it denotes name which i don't want.




2) i have quite large data where some entries like this

24 lon nik-fty brn dp

29 brn uvw-hft tyu




where brn show both vehicle and location. so i have to match vehicle and location combination like this


* brn *
* brn * 2


it shows value 2 while it should return 0 value. i am using countifs(). in this query always any random number comes before first *brn*(like second row). i don't know how this concept to use in formula.


thanks in advancee

[samba_ravi]

Attach sample excel file with expected result

[anu08]

i have attache sample file

[samba_ravi]

D3=COUNTIFS($A$1:$A$10,D$2&"-*",$A$1:$A$10,"*-"&$C3)+COUNTIFS($A$1:$A$10,"*-"&D$2,$A$1:$A$10,$C3&"-*")

Please Login or Register  to view this content.

Try this and copy towards down

[anu08]

Thanks it is working.......how to solve 2nd ppart of problerm (*rst* matching with *rst* problem)

[samba_ravi]

It is also giving the result as 0

[anu08]

thanks for ur prompt reply it is working