Count Multiple Unique Values in One Cell

In column B I have multiple categories with the total number of people in each category in parenthesis next to the abbreviated category, e.g. FP(3). There are multiple categories with their totals in one cell, separated by a comma. Column A has the location type.

I need to be able to count the total number of people for each category by location from the entire column. Attached is a spreadsheet for an example.

Assuming each column D value won't appear more than once in any single cell in column B, then try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down