Thread: Finding a Specific Sum in a List of Numbers

I have a list of 111 rows, each one containing different information, including a dollar amount. I also have a number that some (but not all) of those dollar amounts total to. Is there a macro, or some other way, that can automatically search through the numbers to see if any of them total the number I enter? I would also like it to remove duplicate amounts that total 0, for example $1,000 and ($1,000).

I've attached an example.

The main thing I am trying to accomplish is to weed out the duplicate amounts (where one is positive and one is negative), and use the remaining amounts to see if any of them total a certain number, in this example it is the highlighted number in row 116 (256,227.00).

Let me know if I can provide you with any other information that may help. Thanks for looking!

I didn't look at your spreadsheet, but you can always check for a match like this

Column A : numbers
B1: MatchNumber
C1:  =countif(A:A, B1)

if you want to get fancy: =if(countif(A:A, B1)>0, "Yes, at least one match", "Nope, there is no match")

Originally Posted by WHER

Using helper columns and Solver, i got this result:

Thank you very much Wher. Is there anything that can be done to see if any of them add up to the opposite of the number? So in this sheet, it would search for numbers that total 256,227 instead of (256,227)?

Thanks again!

I reconfigured Solver to look for a total of 256,227; it ran for several minutes but couldn't find a solution.

Originally Posted by WHER

I reconfigured Solver to look for a total of 256,227; it ran for several minutes but couldn't find a solution.

Thanks again Wher. Could you possibly tell me how to do this on my own? I have numerous other spreadsheets I would like to try it on. I assume it is more than just copying and pasting the formulas.

The attachment in post #3 is modified (explanations added)

Originally Posted by WHER

The attachment in post #3 is modified (explanations added)

Thank you WHER, I appreciate you going out of your way to lay it all out for me. I wish I could rep you again!