This seems to me to be quite different from what you asked in your original post (many non-contiguous ranges).

First of all I find the design of sheet Transfers to be rather inappropriate from a formula building perspective. Do you absolutely want to stick to that design or would you be prepared to consider another (database) design?

Furthermore, if I shall go on, please upload a new file in which you make some entries and show manually the expected results in the other sheet.