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

  • Thread starter Thread starter sit
  • Start date Start date
S

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|
+----------------------------------------------------------------+
 
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
 
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.
 
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..
 
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
 
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>
 
LOL - Wait till Mrs Peo finds out you have added a new currency to the ones you
normally accept in payment :-)
 
Back
Top