how to locate sum of which cells matches the number entered in a single cell?

[sit]

Well!
Imagine, an excel worksheet with a really long column with numbers,
and a single cell with a number in it.
Is there a way to find out, the sum of which cells matches the
number entered in that single cell?
It's a tricky one isn't it?


+----------------------------------------------------------------+
| Attachment filename: example.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=357884|
+----------------------------------------------------------------+

 

[Ken Wright]

What's a really long column??

Depending on how long it is and how long you want to wait while Excel look for
the answer, you could try this from a previous post of Tom Ogilvy's - Just need
to change the references and the target value for your own:-

I arranged your data like this (A1:B62) (this is actually the solution).
Each number was in column A and column B contained 1's (B1:B62) (I took
out any rows with spaces).

I created defined names

Insert=>Name=>Define

Name: Data
Refers to: =Sheet1!$A$1!$A$62

Name: Flag
Refers to: =Sheet1!$B$1!$B$62


In D1 I put in the formula

=SumProduct(Data,Flag)


I then did Tools=>solver. I selected

Set Target Cell: = *$D$1*

Equal to: Value of *1391.03*

By Changing Cells *Flag*

I clicked Add Constraints

In the first box I put *Flag* and selected *bin* from the dropdown

I then told solver to solve (Clicked the solve button) in the below, the
numbers with a 1 next to them sum up to 1391.03

Tom Ogilvy

 

[cmoore]

I don't think there is an easy solution. I would write a macro to first
determine the total number of combinations which depends on the number
in your 'black cell' (See COMBIN function). From there I setup a
nested loop in the macro and use the SUMIF function to evaluate each
combination.

 

[Peo Sjoblom]

Here is one way how to do it, assume the numbers are in column A from
A2 to A30, the single cell with the sum is C2, in B2:B30 put 1 like


1
1
1
1
1

and so on so that all the numbers in A have and adjacent value of 1 in B

now in D2 put

=SUMPRODUCT(A2:A30,B2:B30)


make sure Solver is installed (look under tools>options>add-ins), if not
check it and install it.
start solver under tools>solver, set target cell $D$2, select Value of and
put the value from the single cell
in that box. in the by changing cells box put $B$2:$B$30, then click add in
the subject to constraints
and put $B$2:$B$30 there, from dropdown select bin and click OK. Now click
solve and wait..
When solver is done select keep solver solution, as you can see the 1 column
has changed to
1s and 0s, if you sum the values in A that have a 1 next to it that is the
numbers to sum..
You can apply the autofilter (put a header for both columns and do
data>filter>autofilter),
then filter on 1, select the visible cells and copy somewhere else..

 

[sit]

It really works!
Thank you. I've tryed the Peo Sjoblom advice and it works just fine
Dear Peo I'll buy You a beer, you say when and where.
Thanks for help to all of you guys, and i promise to try all th
advices now

 

[Ken Wright]

My repost of Tom's solution was the same as Peo's, so stick with what you have.

 

[Peo Sjoblom]

I don't know the originator of it, I once picked it up from a Myrna Larson
post..
Of course that doesn't mean I won't have that beer/ <vbg>

 

[Ken Wright]

LOL - Wait till Mrs Peo finds out you have added a new currency to the ones you
normally accept in payment