I have a total number but don't know how it made up from a range of data

[mr_vic]

All,

This is my first post on this forum. After spending the morning searching for the answer, i came across this forum and i am hoping you can help me.

I have a total value on a excel 2007 spreadsheet. This number is hard coded and is made up of a series of numbers from a data range.

Is there a way that I can sum the items in the data range to bring back my total?

An example is below:

Have a total of 100 and it is made up from the following data range

20
20
20
20
20
10
____
110

I know that the 5 items of 20 make make the 100 i am looking for. Can excel do this for more complex sums?

I've tried goal seek and sumif(s) and got no where.

Can anyone help.

Thank you

James

[davesexcel]

Using solver, you may have to activate the add-in.
http://kb.wisc.edu/cae/page.php?id=5602

In the example provided, 1 is placed beside the other number
D1 has the formula =SUMPRODUCT(A1:A6,B1:B6)
Goto Tools=>solver
Target cell=D1
Changing cells B1:B6
b1:b6=binary (go to Change then select B1:B6 then in the dropdown select bin
click ok
then solve
The values that make up the total will have a 1 beside it, the others will become 0

The macro does this as well, then highlights the cells that total the value you are looking for