Counting How Many Times Range of Numbers (ZIP Codes) Appears Within Another Range(Full Add

Hi Guys,

Any help on this would be greatly appreciated. I've been searching the Internet for a few hours on how to accomplish the below scenario and have been unsuccessful (I may just not know what the correct terms to search for are). Here it is:

Say I have a column (A) in my spreadsheet with full addresses, for instance:

A2= Aaron Rodgers, 123 Packers St., Green Bay, WI 10001-0001
A3= LeBron James, Cleveland Cavs, 23 King St., Cleveland, OH 10003-1325
etc.

Then I have another column (B) with ZIP Codes like:

B2= 10001
B3= 10002
B4= 10003
etc.

What my goal is is to count how many of the full addresses from A contain one of the ZIP codes from B. I have attached a sample spreadsheet to hopefully make it easier. sample.xlsx

I've tried quite a few different things with counts and wildcards but the closest I can get is using this long formula I found on another forum:

=SUMPRODUCT((LEN(range of addresses)-LEN(SUBSTITUTE(range of addresses,cell with ZIP code,"")))/LEN(cell with ZIP code)) - The problem with this is that you need to do this formula for each cell with the ZIP code and I have about 1,000 ZIP codes to work with, which is not feasible.

If this matters, in the actual spreadsheet I have the data is on separate sheets within the workbook with about 10K addresses and about 1,000 ZIP Codes.

Again, any help would be great! I really appreciate this community as you all provide such great information.

Thanks in advance.