search 2 criteria and if it matches give me the number of matches

I have 2 files. 1 files is the data and the second file is what I am trying to get from the data. I have it separate because the data file changes every month.

For all fields in F (Data file) that matches the word like “Arbor Brook” for A5 (Formula file) and “Business Essentials” (B2 in Formula file) it gives me a count. So for this one it should return 3.

How do I create a formula that will do that and also if possible I don’t want to open the Data file. As I get new data files each month I will just name it the same so it knows which file to use.

Also want to do that for "Business Premium" and property name.

Thank you
excel2016Data.pngFormulaFile.png

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I uploaded the 2 files.

Try

in B3

=SUMPRODUCT(--ISNUMBER(SEARCH($A3,'C:\Users\xxxxx\Downloads\[Office365UsersTest.xlsx]Office365Users'!$A$1:$A$1000))*--ISNUMBER(SEARCH("Essentials",'C:\Users\xxxxx\Downloads\[Office365UsersTest.xlsx]Office365Users'!$B$1:$B$1000)))

in C3

=SUMPRODUCT(--ISNUMBER(SEARCH($A3,'C:\Users\xxxxx\Downloads\[Office365UsersTest.xlsx]Office365Users'!$A$1:$A$1000))*--ISNUMBER(SEARCH("Premium",'C:\Users\xxxxx\Downloads\[Office365UsersTest.xlsx]Office365Users'!$B$1:$B$1000)))

File can be closed

just change Filepath to yours