Tried formula variations including "if", "sumif", "min", "match" but can't get it to work.

Hello,

I would really appreciate if someone could help me with the example attached.

Column A lists potential regions to import from, and column B lists distances from those regions to my location (Trondheim). Column C lists the quantity produced in those regions, and in Column D I have a target of x kilos that I wish to import. In column E I have a CO2-factor that converges km*kg to kgCO2 produced. I want to import from the regions closest to me (shortest distance), and combined, the quantity from those regions should match my target of x kilos.

To do this I need a formula that 1) sorts out the lowest values in column B, 2) sums up the values of the adjacent cells (quantity) until target value for quantity is reached, and finally, 3) all cells in column B that gets counted should be multiplied with their adjacent cells and then multiplied with the CO2-factor, before they are all summed together (distance x kg x CO2-factor).

The purpose is to find the lowest number for CO2-emissions.

Thank you in advance,

Bjorn

Hi Bjorn, and welcome to the forum,

Please read the yellow banner at the top of the page. Or skip to extended version of it below:

Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If there are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, empty cells between data, mixed text/numbers, merged cells, some important cells filled with content by macro/manually/by formula, etc. - please show them all, or at least indicate in text). The use of unrepresentative data and formats is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following (paperclip icon does not work!): Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

PS. The solution probably would be easier in Excel but I think it is still manageable in '16 version. Is it OK to use some helper column in solution? It would probably make life much easier (and formulas shorter). Just probably - I've not started solving, as there is no sample file.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)