## Find numbers closest to sum

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to total? (2): Multiple Solutions. The MMULT function is used in really clever way, I thought that function was pretty much useless.

I modified the formula in excelxor's post so it can identify the sum that is nearest to the target value, if there isn´t a total equal to target value.

The animated picture shows two target values in cell E1. The result is in cell range E6:L6 and the sum is in cell M6.

Array formula in cell range E6:L6:

### How do I enter this array formula?

- Select cell range E6:L6
- Paste the array formula in the formula bar
- Press an hold CTRL + SHIFT
- Press Enter
- Release all keys

If you did it right the formula now begins and ends with curly brackets, like this {=array formula}. They appear automatically, don´t enter the curly brackets yourself.

### I want to use this formula, how do I change it?

The array formula uses this cell range $B$2:$B$9, if you need to work with more values like $B$2:$B$15 , you can change the formula following these steps.

- Copy the formula to notepad
- Press CTRL + H
- Search string $B$2:$B$9
- Replace string $B$2:$B$15
- Click "Replace all" button
- Copy the formula from notepad
- Delete the old formula in excel
- Enter the new array formula in E6:Q6, see steps above.

### How many numbers can I use?

If you have more than 15 numbers I recommend using my udf: Excel udf: Find positive and negative amounts that net to zero I think my udf can handle up to 20 maybe 22 values, it depends on your computer hardware.

Torstein made a great comment below: *Is it possible to restrict solutions to those which have eg three numbers that adds to the total?
*Yes, it is. See his question and my answer with a workbook you can download.

### I want to learn more about advanced formulas?

I have made an advanced excel course that is available online, check it out.

### Recommended reading

Which numbers add up to total? (2): Multiple Solutions

Which numbers add up to total?

Excel udf: Find positive and negative amounts that net to zero

Identify numbers in sum using solver

Find positive and negative amounts that net to zero

### Download *.xlsx file

Identify numbers in sum using Excel solver

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]

Find positive and negative amounts that net to zero [UDF]

Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]

Extract records containing digits [Formula]

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]

List permutations without repetition [UDF]

This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

### 19 Responses to “Find numbers closest to sum”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Thanks, Oscar!

Looks like you've managed to successfully amend the formula for this new condition (though what have you done with my Defined Names? :-)) Anyway, good work!

Also, I like the fact that you've replaced the ROW/INDIRECT constructions in my solution with the preferred ROW/INDEX ones. I myself am using this version now, though I haven't got the time to go back into all of my old posts and update them all!

Nice-looking site, by the way.

Keep up the good work.

Regards

XOR LX,

Thank you!

though what have you done with my Defined Names? :-))Perhaps it is easier to use defined names, I don´t know. It sure makes the formula look smaller but I want to reveal the truth. ;-)

Hi Oscar and XOR LX!

I'm really impressed what you guys can do with excel formulas. I have been trying to figure out permutations for so long, but have not been able to make it.

One question to the formulas: Is it possible to restrict solutions to those which have eg three numbers that adds to the total?

Hope I make my self understood!

Regards Torstein

Torstein,

Thanks!

This sheets allows you to specify the number of values you want, cell J1. If the target value is equal to the sum, cell M6 is formatted yellow. If not equal, cell M6 is colored red.

I am sure this formula can be made smaller but I don´t know how to build a permutations array that only uses three out of 8 values, I leave that to XOR LX. :-)

Download excel *.xlsx file

Find numbers which are closest to a total_permut.xlsx

"I am sure this formula can be made smaller but I don´t know how to build a permutations array that only uses three out of 8 values, I leave that to XOR LX. :-)"

That's precisely what I'm looking into now!

Interesting exercise in itself.

Bear with me!

Cheers

XOR LX,

Thanks for looking in to that, I am very interested.

Done!

https://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/

Haven't yet managed to reduce the required array of zeroes and ones, so queried it with a further IF clause. More resource, I'm afraid, though not a great deal by the looks of it. Functions quite nicely, actually. :-)

I'll keep looking into ways in which we may be able to generate such reduced arrays, though. Interesting mathematical exercise!

Enjoy for now!

Thanks so much for this fantastic post!

Is there a way to find closest combination greater than or equal to the target value?

Evgeny

Thanks, yes there is.

https://www.get-digital-help.com/wp-content/uploads/2015/02/Find-numbers-which-are-equal-or-above-to-a-total.xlsx

Hi Oscar,

This is exactly what I've been looking for...

I just want to add an extra rule into this formula that would exclude all the values that are higher than 260. Are you able to help me with this extra requirement?

Thanks in advance and congrats for sharing your knowledge.

JRM

Thanks so much for this fantastic post!

Is there a way to do this for more data? I sometime need it for more that 100 items.

Thanks in advance and congrats for sharing your knowledge.

thanks oscar, awesome post as always.

1 question: can the array formula work in vertical format, at column C2:C9?

this would be helpful when the list has other information.

lohhw3,

Thank you. Yes, you can.

1. Select cell range C2:C9

2. Enter the same array formula in the formula bar, except use transpose function around the entire array formula, like this:

=TRANSPOSE(arrayformula)

3. Press Ctrl + SHIFT + ENTER

Thanks for this! I saw you offered a version for a combination greater than or equal to the target. I'd really appreciate it if you could show me how to go about changing it to be LESS than or equal to the target. I fiddled around with it for a while but it's beyond me.

SC,

I think I got it working.

Find-numbers-which-are-equal-or-less-than-target-value.xlsx

Hi Oscar,

This is amazing, and incredibly interesting. Couple of quick questions, would there be a way to change the formally so you can both pick the amount of numbers in the solution and also only find a solution that's greater than your number. Also, would there be a way to have the formula also look at having multiples of one number if that is closer than any pother combination (e.g. if I had 1,3,20,30 and I want to get to 26 using 3 numbers it would spit out two 3's and one 20 instead of one 1, one 3, and one 20). Really appreciate your hard work putting this together.

Josh Cohen,

Would there be a way to change the formally so you can both pick the amount of numbers in the solution and also only find a solution that's greater than your number.Try this workbook.

Find-numbers-which-are-closest-to-a-total2.xlsx

Also, would there be a way to have the formula also look at having multiples of one number if that is closer than any other combination (e.g. if I had 1,3,20,30 and I want to get to 26 using 3 numbers it would spit out two 3's and one 20 instead of one 1, one 3, and one 20)I believe you need a custom function to do that.

Thank you, I really appreciate this. This is my very first time using excel and "Find numbers closest to sum" was a daunting task... I really appreciate the step by step way that you did this, people must forget that they had a first time and no matter how hard they try they always leave something out of their description and that really is a downer. (You didn't put the step in about how to sum everything at the end, but I figured it out after 2 or 3 hours). Thanks again..

I have a similar problem with an Array of as many as 26 rows and 6 columns.

Is there a way to adapt this so that is selects one number form each row

such that the sum of that row adds up to as close to the target as possible.

Then does the same for all 26 rows. So that no 2 values are repeated,

and the sum of each row is as close as possible to the (Target) Mean sum of all rows?