Issue with countifs formula

Godders199

Active Member
Joined
Mar 2, 2017
Messages
313
Office Version
  1. 2013
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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))
 
Upvote 0
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))
 
Upvote 0
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.
 
Last edited:
Upvote 0
Re: Issue with countifs forumla

thanks, this one does not work I will keep trying
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top