Array formula to remove helper column

On the attached, on worksheet "schedule", in column D I have a formula that checks whether the corresponding value in column C exists in column B on the worksheet "loads", .

I have then used COUNTIFS to see how many entries exist for each location and the final result is shown on the "results" worksheet in column C.

I want this to work without the need for the helper column ("schedule!", column D).

Can this be done with an array formula?

Thanks.

=SUMPRODUCT((schedule!B:B=B2)*ISNUMBER(SEARCH(loads!B:B,schedule!C:C)))
full column is not a good idea

Try this in C2:

=SUMPRODUCT((schedule!B$2:B$48=B2)*(COUNTIF(loads!B$2:B$69,"*"&schedule!C$2:C$48&"*")))

I believe your helper column is producing incorrect results so this will not match up with what you have currently.

Thanks both - sorted.