Find a number combination in a group of cells

**OpieWinston** · 10-28-2011, 04:08 PM

I'm looking for another excel game changer (for the work I do anyway).

I have a dollar amount, and I want to know if any combination of dollar amounts in a particular range of cells will equal that dollar amount. Is this possible in Excel?

Example: I have 20 different dollar amounts in a column. I want to know what combination of those 20 different dollar amounts, if any, will equal $257.97. The dollar amount I'm looking for and the numbers in the range will change with each use. I'm hoping for a formula, but VBA will work too as I could just make a template and copy / paste the numbers in.

I've come to learn to never doubt excel, but I have to admit that I'm having doubts on this one.

**MarvinP** · 10-28-2011, 05:51 PM

Hi Opie,

I did a problem like this a few months ago. See if the attached helps. If not then supply a sample file that we can work with to solve. To attach a sample, click on Go Advanced and then the Paper Clip Icon above the advanced message area.

**Alf** · 10-29-2011, 12:19 PM

Question MarvinP, could you please explain why you use range "A2:A51" in your formula?

I would have thought "A2:A50". I also did a test with solver but your solution works much faster!

Alf

**MarvinP** · 10-29-2011, 06:13 PM

Hi Alf,

I guess I used A2 to A51 because that was how many numbers there were to pick from in the original problem. Feel free to change the ranges if more numbers are needed.

I think we, as humans, get stuck on needing a formula to calculate things. Computers never get tired of guessing so I like doing random picks of numbers to see if they add up. In this problem it was a little different as the OP didn't say how many numbers needed to be used to sum to the total. I'm not sure my code did it correctly but the idea was still valid.

**martindwilson** · 10-29-2011, 09:07 PM

you could just use solver

**Alf** · 10-30-2011, 02:47 AM

Thans for explanation MarvinP. Reason I asked was that changing the range A2:A51 in your formula to A2:A50 and running the macro gives me a #REF! value and I can't understand why.

Alf

**thisbenelestrange** · 03-15-2014, 10:45 AM

hey! i was wondering if it is possible to not get a combination from a certain group of cells... for instance, 1,2,-3 is not to be matched with each other; rather, they are to be matched against 5,3. Thus, leaving only 1,2 (from first group) and 5 (from the last group) as outstanding items. PS: i'm getting the combinations that:
a. equal to zero
b. from different groups/sets; and not with each other

Thanks for your reply