count how many times text string appears in column

[ajm]

i am using if/countifs to test for the existence of particular strings of characters (company names) within a column on another tab.

my source sheet contains company names in column B and today's date at O1. The Contract Extract tab contains Company names in Col E and Dates in Col F. so, if the string in col B matches one of the companies in Col E on the Contracts Extract tab, and the date in Col F is greater than O1 on the source sheet, give me a yes. this is my formula:

IF(COUNTIFS('Contract Extract'!$E:$E,B5,'Contract Extract'!$F:$F,">"&$O$1)>=1,"Y","N")

in the list of companies in Col E of Contract Extract, there are several entries containing multiple companies (where there has been a consortium of bidders for eg). I need the formula to be able to count those instances also. the formula above, which i thought should do that, does not.

using consulting firms as an example. say i am looking for PricewaterhouseCoopers. the list of company names includes an entry for "KPMG, [Various - Parent Contract], PricewaterhouseCoopers, Grant Thornton Australia Ltd, PKF Brisbane Audit, BSI Group ANZ Pty Limited"

my current formula does not count this as a match. what can i use instead?

 

[hagia_sofia]

Hello, try to use COUNTIFS with wildcards:

IF(COUNTIFS('Contract Extract'!$E:$E,"*"&B5&"*",'Contract Extract'!$F:$F,">"&$O$1)>=1,"Y","N")

 

[ajm]

Hello, try to use COUNTIFS with wildcards:

IF(COUNTIFS('Contract Extract'!$E:$E,"*"&B5&"*",'Contract Extract'!$F:$F,">"&$O$1)>=1,"Y","N")

that did the trick nicely. thanks