Countifs Partial Match

[Godders199]

Hello, i am trying to modify a formula to count partial matches, my current formula is

=COUNTIFS(Allocation!H:H,'Instructions '!L3,Allocation!AB:AB,"<="&EOMONTH(TODAY(),0),Allocation!AB:AB,">"&EOMONTH(TODAY(),-1))

which works correctly , for matches in the current month. However, I am now having to use a second feed into excel from Sharepoint which contains names from outlook which differ from all the other feeds. So i just need to change the first element of the formula =COUNTIFS(Allocation!H:H,'Instructions '!L3

Allocation H:H is the name in Outlook, Instruction Ls is the name in all the other systems. i tried wrapping Instructions L3 in "**" but this did not work.

an example of what i have is that Outlook might say Fred 1 smith , where all other systems just say fred smith ( instructions L3), so i just want to count all any row where in column H H the name in instructions L3 is contained.

I am unable to change the names in my search criteria, as it is only outlook which has added numbers or middle names , initials to create unique account names.

Have been searching google but unable to find an example, so i hope this is possible.

 

[RoryA]

For an example like that, you might use something like:

=COUNTIFS(Allocation!H:H,SUBSTITUTE('Instructions '!L3," ","*")...

 

[Godders199]

thanks, I will give it a go