## Find numbers closest to sum

*Article updated on February 20, 2018*

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

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

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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