Formula for sum of multi-dropdown with two variables

[TDL-MJ]

Here is the table set up: 

Column A	Column B	Column C	Column D	Column E	Column F
Date	Person	Details	Amount	Category	Split
12.1.22	One	Notes	12.00	Food	1
12.2.22	One, Two	Notes	20.00	Supplies	2
12.2.22	One, Two, Three	Notes	55.50	Food	3

I am attempting to pull the following information: 

Person One	Spend
Food	*Formula here to pull sum
Supplies	*Formula here to pull sum

The issue I am having is getting the formula to filter when column B has more than one name.

Column B is a multi-select drop-down using the following: 

Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 2 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

I used the following to filter results, but it will not filter the data if more than one person is listed in column B. 

=SUMIFS(D2:D8,B2:B8,"Person One",E2:E8,"Food")

I also attempted to use SUMPRODUCT, but I have the same issue. It is only pulling column B if there are no other persons listed. 

=SUMPRODUCT(ISNUMBER(SEARCH("One",$B$2:$B$8))*$D$2:$D$8)

If anyone has some insight, I would be very appreciative. 

Thanks. 

[#2]

@TDL-MJ 

=SUMPRODUCT(ISNUMBER(SEARCH(RIGHT($A$9,3),$B$3:$B$5))*($E$3:$E$5=A10)*$D$3:$D$5)

You can try this formula.

 

[#3]

@TDL-MJ 

SUMIFS with wildcard:

=SUMIFS($D$2:$D$4,$B$2:$B$4,"*One*",$E$2:$E$4,I2)

SUMIFS if you have the 'person' in a cell to refer to:

=SUMIFS($D$2:$D$4,$B$2:$B$4,"*"&$I$1&"*",$E$2:$E$4,I2)