Issue with countifs formula

[Godders199]

I have the following formula in a spreadsheet which works correctly, however we have a new team member and the count does not work for them, i presume it has something to do with " ' " in their surname.

=COUNTIFS('Sharepoint history'!H:H,SUBSTITUTE('Instructions '!L3," ","*"),'Sharepoint history'!E:E,"<="&EOMONTH(TODAY(),0),'Sharepoint history'!E:E,">"&EOMONTH(TODAY(),-1))

In sharepoint H:H the name is fred O'tool
In L3 it is Fred otool

I can not change the name in L3.

As i said it works correctly for all other 50 names where there is not a complete match, any help would be appreciated.

 

[Special-K99]

Re: Issue with countifs forumla

Does this work?
Note substitution of apostrophe to null

=COUNTIFS('Sharepoint history'!H:H,SUBSTITUTE(SUBSTITUTE('Instructions '!L3," ","*"),"'",""),'Sharepoint history'!E:E,"<="&EOMONTH(TODAY(),0),'Sharepoint history'!E:E,">"&EOMONTH(TODAY(),-1))

 

[Aladin Akyurek]

Re: Issue with countifs forumla

Try something like...

=SUMPRODUCT(--(SUBSTITUTE('Sharepoint history'!$H$2:$H$1000,CHAR(39),"")='Instructions '!L3),--('Sharepoint history'!$E$2:$E$1000<=EOMONTH(TODAY(),0)),--('Sharepoint history'!$E$2:$E$1000>EOMONTH(TODAY(),-1))

 

[Special-K99]

Re: Issue with countifs forumla

Does this work?
Note substitution of apostrophe to null

=COUNTIFS('Sharepoint history'!H:H,SUBSTITUTE(SUBSTITUTE('Instructions '!L3," ","*"),"'",""),'Sharepoint history'!E:E,"<="&EOMONTH(TODAY(),0),'Sharepoint history'!E:E,">"&EOMONTH(TODAY(),-1))

Oops that wont work.
I think it should be

=COUNTIFS(SUBSTITUTE('Sharepoint history'!H:H,"'",""),SUBSTITUTE('Instructions '!L3," ","*"),'Sharepoint history'!E:E,"<="&EOMONTH(TODAY(),0),'Sharepoint history'!E:E,">"&EOMONTH(TODAY(),-1))

Am not even sure this will work to be honest.

 

[Godders199]

Re: Issue with countifs forumla

thanks, this one does not work I will keep trying

 

[Godders199]

Re: Issue with countifs forumla

Oops that wont work.
I think it should be

=COUNTIFS(SUBSTITUTE('Sharepoint history'!H:H,"'",""),SUBSTITUTE('Instructions '!L3," ","*"),'Sharepoint history'!E:E,"<="&EOMONTH(TODAY(),0),'Sharepoint history'!E:E,">"&EOMONTH(TODAY(),-1))

Am not even sure this will work to be honest.

Hi, something is wrong with the format of this, it just highlights the whole formula, I cant see where it is wrong

 

[Aladin Akyurek]

Re: Issue with countifs forumla

thanks, this one does not work I will keep trying

Did you try the SUMPRODUCT proposal?

=SUMPRODUCT(--(SUBSTITUTE('Sharepoint history'!$H$2:$H$1000,CHAR(39),"")='Instructions '!L3),--('Sharepoint history'!$E$2:$E$1000<=EOMONTH(TODAY(),0)),--('Sharepoint history'!$E$2:$E$1000>EOMONTH(TODAY(),-1))

Does L3 ususally occur as is in a column H cell or just partially?