need help counting of combination of specific text in textphrase

[anu08]

22 abc ntc-jkl hft ac *abc*

50 ser ghi-bvn kun dp *hft* 2

24 lon nik-fty nik ro *kun* 0

29 abc uvw-hft tyu *nik* 0


i am trying to count how many times abc appears with hft. i am using =COUNTIFS(B1:B5,D3,B1:B5,E2) formula where d3 is *hft* and e2 is *abc*. But i want result 1 insted of 2 because hft in first row is location and hft in fourth row is name. i want abc combination with location only . thanks in advance

[canapone]

Hi,

just an attempt

in D3 instead of

*hft*

you could try

* hft*

Hope it helps

[anu08]

thanks for ur quick reply it works and solved most of problem..but there is one thing in some data where 29 abc hft-uvw tyu then it counts again will count *hft* .. how it will solve

[canapone]

Hi,

thank you for your kind feedback.

Another attempt, maybe an extra spase at the end of the substring in D3 could do the trick.

* hft *

Hope it helps

[samba_ravi]

Attach sample file excel file with possible examples and expected result

[anu08]

Great!! thanks you have solved my problem

[canapone]

Hi,

thanks for sharing your feedback.

नमस्ते

[anu08]

Buona sera

i have two queries
1 21 xyz nab -gyt huy *xyz*() *buy*
32 buy der -kon htr * huy * 1
* nab * 1
* htr * 1
while it should return 1 only in 1st row (with * huy *) in second row it should return 0
2 44 rfb uid-efg grn
25 grn tys-swa chu * grn *
* grn *2
while it should return 0.
Actually i have quite large data where i found these type of entries here first grn is refrence number and last grn is location..i have task to count the combination of refrence number and location. I am using countifs formula like previous one. in second query it is clue that always number comes before first grn like (25 grn tys-swa chu).but i don't know how to use it
sorry!! for again brothering u. thanks in advance,