Sometimes we are in a situation where we have a target figure plus a series of values and we want to know which, if any, combination of those values has a sum which is equal to the target.

This can be done as follows:

*Edit: this post has now been revised here to account for multiple returns, should that be a requirement.*

Using the above set-up, with our target value in A2 and our (in this case 9) values in C1:K1, we will place formulas in C2:K2 which will contain an “X” if the value in the row above forms part of our solution.

In the example given, you can easily verify that 10+20+12+5 does indeed equal 47.

First, as I often like to do, we will define the range of values, C1:K1, as *Range1* using Name Manager (*Formulas* tab). This solution will work for other ranges as well, of more or less than 9 cells, though readers should note that this solution is designed to work with a *horizontal* range of values: for the equivalent set-up using a *vertical* range, see the comments at the end of this post.

We then make two further additions to Name Manager, viz:

*Name:* Arry1

*Refers to:*

=ROW(INDIRECT("1:"&COLUMNS(Range1)))

*Name:* Arry2

*Refers to:*

=ROW(INDIRECT("1:"&2^COLUMNS(Range1)))

The **array formula** in C2 is then:

=IF(ISNUMBER(MATCH(COLUMNS($C:C),IF(INDEX(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2),TRANSPOSE(Range1))=$A$2,0),),TRANSPOSE(Arry1)),0)),"X","")

Copy across as required.

*Notes*:

1) This set-up has a theoretical limit on the size of the dataset of 20 values. However, it is more than likely that Excel will run out of resources in that case due to the sheer size of the calculations involved. I ran it with a dataset comprising 15 values, i.e. C2:Q2, and it just about coped, taking 5 seconds or so to calculate.

However, I would imagine that any more than that and Excel may begin to struggle, and so readers hoping for a solution with a dataset of such a size should probably seek a VBA-based solution instead

2) If no combination of the values exists for which the sum is equal to the target value then all cells remain blank.

3) If more than one combination of values has a sum equal to the target value then one of those combinations is given (the actual reasoning behind which one is chosen is relatively complex, though again, if anyone is interested I would be happy to expound.)

### Like this:

Like Loading...

*Related*

Brilliant!! I’m interested for the logic of the formula

Thanks! I’ll try to get round to adding an explanation soon: either to the end of this post or in a follow-up

Love it !!!

Just a tiny question : should the target be equal to a single number, why can’t the formula spot it ?

Hi James and welcome to the site! 🙂

Thanks – glad you like it! Can you just clarify what you mean by your statement – 47

isa “single number”. What did you mean by that?Cheers

Hi,

With the same set of numbers, if I were to type in 12 as a target … the formula will produce 10 and 2 … despite the fact 12 is located after 2 …

Also, I would be very interested in the “vertical” version of your great formula …

Cheers

I see. Yes – see my point 3) above, since this is technically a case of there being more than one combination which meets the target, despite the (rather obvious) point that one of those “combinations” is in effect a single value.

If you put the 12 before the 2 and 10 in the list then it will indeed highlight the 12. The construction I developed operates more on a left-to-right basis than on a smallest-number-of-values-which-makes-up-the-total basis: put these numbers in C1:G1: 4, 4, 4, 6, 6 and the formula will prefer the first set of three to the shorter set of two.

Perhaps this is something I could work on, though to be honest I developed this more as a result of coming across this type of question on forums where it seemed that

anycombination which met the total was seen as a good thing, nevermind trying to pick the best of several such matches! Still, might be something in looking at that scenario as well.Re the vertical version, I’ll try to post that variation later this week in this post.

Thanks a lot!

To give you a little bit of background … in the world of accounting, in terms of bookkeeping, there is a specific need to reconcile bank accounts … with debits(invoices) on one hand and, on the other hand, either one or several credits (payments received)…

I sincerely believe your formula could be of a lot of assistance in this regard …

Cheers

Hi again.

The equivalent set-up for a vertical range, assumed to be in C1:C9, and stored as the Named Range

Range2, is:Define

Arry3as:Define

Arry4as:The main

array formulain D1 is then:Hope that helps!

Regards

XLOR LX,

Many thanks for the “vertical version” …

I will take the time to fully study it …

Cheers

You’re welcome!

Hope you find some good uses for it in the financial world! And get back to me if you have any questions.

Regards

XOR LX: I really appreciate the work and creativity that went into developing your formula. I also understand that what I’m about to say is contrary to the point of your blog.

Having said that, I’d bypass any formula-driven solution and use Excel’s built-in solver, which is straightforward to setup, and targets these types of problems. Also, there is (at least) more than one solution to this problem – 10 + 20 + 17 = 47.

Really awesome blog you have – looking forward to your future posts.

Forgot to mention that the above solution is indeed one of several, and that 10 + 20 + 17 was the first one produced by solver, without additional constraints (e.g. number of values required). Seems there are five total solutions, depending on how many values are to be summed.

Still in all, I’m very impressed at your solution via the formula approach. Very Excel Hero-like.

@VictorMany thanks for your kind words. And yes, you are no doubt right that there are better means, i.e. Solver, for solving this type of problem in Excel. Perhaps this is one case where, for the sake of theoretical interest only, I’ve pushed the boat out too far. That said, it does at least demonstrate some interesting possibilities for formula-work, don’t you think?

Would you perhaps be willing to post a quick breakdown of how you would go about achieving the results for the above dataset using Solver? It may well be of interest to readers who stumble across this site, and who do not want to have to implement the formula-based solution I present (or perhaps are put off by its limitations re the size of the dataset).

No worries if not. Thanks a lot for your comments and contribution, and look forward to hearing more from you in future!

Regards

@XOR LX

There are plenty of other folks on the web that do a much better job of explaining how to set up and run through solver – I could never do it justice, particularly for the bright folks that peruse this site.

However, I will say that chandoo provides a well-written tutorial, and trump excel has a five-part data analysis series in progress, which will touch on goal seek as well as solver.

I use solver for fairly rudimentary optimization (sometimes transit/parking reimbursements haha), and other max/min scenarios. Having said that, I think solver is an extremely powerful tool that is relatively unknown, and therefore, underutilized.

Pingback: Find numbers closest to sum | Get Digital Help - Microsoft Excel resource

Thank you ery much XOR LX. your formulas are life saving 🙂

@FloraGlad to hear it! And you’re very welcome! 🙂

Pingback: Math puzzle

I’m trying to make this work, at work. No luck. 😦 Using MS Excel 2013. I created your data example exactly. Copied and pasted the Arry1 & Arry2. Also copied and pasted the Array formula for cell C2. I’m trying to get yours to work so I can apply to a project at work.

@DavidCan you be a bit more precise? What results do you get? Errors? If so, which errors? Did you commit the array formula in the correct manner (using CTRL+SHIFT+ENTER)? Have you re-checked the two Named Ranges (

Arry1andArry2) have rendered correctly after pasting?Regards

My results are blank cells in row 2, no errors. I have now used the CTRL+SHIFT+ENTER method for the array formula in c2, then copied thru k2.

Not clear about the two Named Ranges, in the Name Manager box, but I have:

Name=Arry1,

Value={…},

Refers To=

Scope=Workbook

Thanks for your help!!

And I take it you’ve also made definitions for

Arry2andRange1, as described?Also, are you using the data as I posted, or your own values? If the latter, have you seen point 2) in the notes?

Regards

I am also having a similar problem to David. I first tried it out on my original problem, but that has 21 numbers. So in case that was the issue, I tried it out on a much simpler problem. I tried the following:

Target is 30 in A2

C1 is 15

D1 is 15

E is 37

Then I named C1:E1 as Range1. Then I copied and pasted the formulas for Arry1 and Arry2. Then I copied the C2 formula into C2. Then I copied the formula into D2 and E2. Nothing has happened.

Should I be copying across in a different way? Did I forget to define something. It feels like I’m missing a key step here.

Thank you so much.

Becky

Hi Becky,

And I take it you entered the formulas within the worksheet as

arrayformulas where instructed to do so in the post?Regards

No, I had not. I didn’t realize that arrays required different syntax than regular formulas. (Which I totally could have gleaned if I had read through the comment section a bit more thoroughly than I did) It works like a charm now. Thank you!

My learning curve on Excel just jumped up exponentially, and as a result there’s a lot of holes in my knowledge. But your explanations are one of the most thorough ones I’ve been able to find. I really appreciate your patience. :3

Glad you got it to work!

And I’m pleased to hear both your kind comments re my work here and also that you appear to be someone who is showing a genuine interest in learning more of this wonderful tool that is Excel!

All the best.

Pingback: MMULT function – Matrix multiplication

This is amazing work!

Couple of quick questions, would there be a way to modify the formula: 1. Allow the user to choose the amount of numbers in the solution subset. 2.Look for the lowest sum that’s greater than or equal to the target. 3. Can use multiples of any number in the set as part of the subset (e.g. if I had a set of {1,3,20,30} and I want to get to 22 using 3 numbers the subset would be {1, 1, 20} instead of {1, 3, 20}

Really appreciate your hard work to this point on this workbook and sharing this it with us.