Optimizing Sum of products where One data set has interchageable values

[Jenol]

Hi,

Can someone please help me to create a formula that involves optimization without using the solver add ins?

The problem is I have two data sets with equal number or entries. I will multiple column A with column B, then add all the products. However, I would like to find the OPTIMIZED sum of products by interchanging the values in column B to find the lowest sum of products. In the image below, "DISTANCE: in column is fixed, but the values in column be can be switched with each other such that it will produce the lowest value in C9.

Thank you!

Capture.PNG

[ChemistB]

I would sort column A highest to lowest and then column B lowest to highest.