Excel SUMIF – check if the criteria range contains a substring of a given cell

I have an Excel worksheet with a large number of sub-accounts and their balances, that have to be aggregated to their accounts, according to a list that maps subaccounts to accounts.
I believe I could use the SUMIF or SUMIFS worksheet functions.

This is the layout:

enter image description here

Column B lists subaccount numbers, and column D the balance for each. In cell Q232, it is shown that the first account aggregates the subaccounts 8002, 8998, and 8001.

For example, if Q232 contained the value "8900;8901", the result in R232 should become the sum of D209 and D210, because B209 and B210 contain 8900 and 8901.

For the future usability of the sheet, VBA is probably not an option. I attempted to use worksheet functions, e.g. in cell R232

=SUMIFS($D$1:$D$216;$B$1:$B$216;SEARCH($B$1:$B$216;Q232)=1)

But this is the wrong syntax. I also don’t see how I can use VLOOKUP, because the range I want to search through (column B) contains only substrings of the subaccount lists (Q232 and below), instead of the other way around.