## 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

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

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]

This article describes a UDF that finds positive and negative numbers that net to approximately zero. You specify the range […]

Gets a value in a specific cell range based on a row and column number.

Rearrange cells in a cell range to vertically distributed values

The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct […]

This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day […]

The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]

Filter records that contain numeric values

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 […]

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]

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. VBA code: How to implement user defined function in […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Sort based on frequency row-wise

In this article I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The second example is a […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Quickly concatenate values into one cell [No VBA]

Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

### 18 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

Use the img tag, like this: <img src="Insert pic link here">

**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..