Text Countifs problem

[Wimpie]

Good day

Please assit wt hthe below
=IF(B7=0,COUNTIFS(Jan!$R:$R,"*"&A7&"*",Jan!$D:$D,$R$1),COUNTIFS(Jan!$R:$R,"*"&A7&"*",Jan!$D:$D,$R$1)*2)

This formula works fine as long as the text A7 does not appear more than oce in a line in column R
e.g if R1 = Cancellation R2 = Cancellaton and A7 is the word cancellation it will count 2, if however,
R1 = Cancellation, Canellaton R2 = Cancellaton and A7 is the word canceltion the formula still gives me 2 an not 3

I need the answer to be 3 irrespective of how the person enters he word,e.g Cancelation Cancelltion, or Cancellarion, Cancellataion, or Cancellation - Cancellation

Would also appreciate it if there is a faster formula that can do this that can be recommended

Thnk you in advance

 

[Keebellah]

This doesn't work with how many time is occurs inside a cell, it just counts IF it is there (at least once)
You should have to add something to check each cell separately for more than one occurrence
The only what is to count the length af the original text minus the length of the text you're looking for.
If the text 'Canc' occurs once the length of the original minus the length of Canc (4) will be 4 less, you will probably have to add a helper column and more.
I am not that good with formulas but there must be a way to count inside each cell too have tried the help in Excel?