summing individual values to match total amount

Hi,

Could it be any number of values from column F which sum to give the total? Or just 2?

Perhaps you can adapt the method I give here to match your needs:

http://excelxor.com/2014/08/26/which...d-up-to-total/

Regards

Yes, it can be any number, not just 2.

I tried to copy/paste your example in my excel file but I'm not getting anything.

You need to enter it as an array formula**:

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Regards

Originally Posted by cassiopea

Thank you. It works now. What do I have to change if I want to expand range to C1:Z1?

Ah. Apologies. I should've mentioned that this solution is limited to a maximum of 20 values, and even then Excel will most likely fail due to the sheer size of the calculations involved.

I ran it with 15 columns' worth, i.e. C1:Q1, and it just about made it, calculating in 5 seconds or so, though I think anything more than that will be unfeasible.

Apologies. I didn't realise that you'd posted this in the VBA section. Clearly in your case any purely formula-based solution will not be sufficient. I will re-bump this to some colleagues who have a bit more experience in VBA than I do and hopefully someone will reply soon.

Regards

Please Login or Register  to view this content.

There is a "brute force" approach (although it works by picking random numbers, rather than cycling through possibilities). You could probably code a bit more intelligence/optimisation into the routine but it would do for a starting point I guess.