COUNTIFS Referencing another workbook

[elliston80]

I have 2 spreadsheets, one is a master file and the other is a calendar where I want to look up and count the number of rows that match 2 criteria. First criteria is a specific date and the second criteria is text beginning with "Email". Below is the formula I have so far. It works as long as the source spreadsheet (master file) is open, however, if I open the calendar spreadsheet without opening the master file, I get a #VALUE ! error. I have read up on this and apparently this function can't reference closed workbooks.

Can someone help me convert this formula to a format that will reference my master file even if it is closed?

Notes:
1. "Activity" is a named range
2. "Date" is a named range

=COUNTIFS('2018 Supplier Marketing Master Plan.xlsm'!Activity,K15&"*",'2018 Supplier Marketing Master Plan.xlsm'!Date,"1/11/18")

 

[FDibbins]

Maybe this?
(untested)
=sumproduct(--('2018 Supplier Marketing Master Plan.xlsm'!Activity=K15&"*"),--('2018 Supplier Marketing Master Plan.xlsm'!Date="1/11/18"))

 

[elliston80]

that worked. thanks for the assist

 

[FDibbins]

Happy to help