Sum amounts from dropdown selections across multiple sheets in Numbers

Unless I'm missing something, this looks like a simple SUMIF()

=SUMIF(Table 1::B3:B100,A2,Table 1::D3:D100)

SUMIF() takes three arguments... a range to search (in this case the range B3:B100) and the value to match (in this case which entry you're trying to sum, entered in cell A2). For each match, it adds the corresponding value in the sum-values range (in this case, D3:D100)

If you set cell A2 in the second table as a pop-up menu with the same entries as those in B3:B100, you can simply select from the popup menu and the SUMIF() will return the sum of the matching values.

Here's a simple example I threw together:

In this case I'm just using Table 1 and Table 2 on the same sheet, but they can easily be on different sheets.

The cells in Table 1::B and Table 2::A2 both have the same pop-up menu of names, and Table 2::B2 has the SUMIF() formula that takes the selected value in A2, matches that against the values in Table 1::B2:B22 and sums the corresponding values in Table 1::D2:D22.

Is that what you're looking for?