Which numbers add up to total? 27

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:

Which numbers add up to total

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

27 comments

  1. Love it !!!

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

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

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

  4. 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!

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

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

  7. XLOR LX,

    Many thanks for the “vertical version” …

    I will take the time to fully study it …

    Cheers

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

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

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

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

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

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

  14. Pingback: Math puzzle

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

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

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

    Thanks for your help!!

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

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

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

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

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

  23. Pingback: MMULT function – Matrix multiplication

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s