# Which numbers add up to total? 28

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

1. John Jairo Vergara Domìnguez says:

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

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

3. James says:

Love it !!!

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

4. Hi James and welcome to the site! 🙂

Thanks – glad you like it! Can you just clarify what you mean by your statement – 47 is a “single number”. What did you mean by that?

Cheers

5. James says:

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

6. 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 any combination 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!

7. James says:

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

8. 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 Arry3 as:

`=ROW(INDIRECT("1:"&ROWS(Range2)))`

Define Arry4 as:

`=ROW(INDIRECT("1:"&2^ROWS(Range2)))`

The main array formula in D1 is then:

`=IF(ISNUMBER(MATCH(ROWS(\$1:1),IF(INDEX(MOD(INT((Arry4-1)/2^(TRANSPOSE(Arry3)-1)),2),MATCH(TRUE,MMULT(MOD(INT((Arry4-1)/2^(TRANSPOSE(Arry3)-1)),2),Range2)=\$A\$2,0),),TRANSPOSE(Arry3)),0)),"X","")`

Hope that helps!

Regards

9. James says:

XLOR LX,

Many thanks for the “vertical version” …

I will take the time to fully study it …

Cheers

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

11. Victor says:

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.

12. Victor says:

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.

13. @Victor

Many 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

14. Victor says:

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

15. Flora says:

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

16. @Flora

Glad to hear it! And you’re very welcome! 🙂

17. Pingback: Math puzzle

18. David says:

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.

19. @David

Can 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 (Arry1 and Arry2) have rendered correctly after pasting?

Regards

20. David says:

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=

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

Scope=Workbook

21. And I take it you’ve also made definitions for Arry2 and Range1, 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

22. Becky says:

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

23. Hi Becky,

And I take it you entered the formulas within the worksheet as array formulas where instructed to do so in the post?

Regards

24. Becky says:

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

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

26. Josh Cohen says:

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.

This site uses Akismet to reduce spam. Learn how your comment data is processed.