I have numbers in 100 cells and I need to figure out which cells out of the 100 add up to a particular number for example 561,674. Does anyone know a forumla? Thanks.
sort them in order. then at the bottom right hand where it shows the sum or count of cells highlighted, change that to sum. then start highlghting the cells from the top until the sum at the right bottom matches the sum you need.
double post
use solver. say you have 100 numbers in column A, then make cells B1:B100 = 1. Now make cell C1 =sum(a1:a100*b1:b100), then hit ctrl+shift+enter to make it an array formula. Fire up solver, and ask it to make cell c1 = 561674, by changing range b1:b100, subject to the constraint that b1:b100 is binary. hit solve, then the rows with a 1 in them are the numbers you need to sum to get to your answer. Although obviously the solution may not be unique.
not to discourage you or anything, but the problem is NP-Complete, meaning it would take a really long time to compute. you need some simplifying assumptions to make the problem easier. http://en.wikipedia.org/wiki/Subset_sum_problem
COUNTIF(Range,561674) should do the trick.
.
Alternatively if you wish to know which cells, rather than how many, you could do the following: 1) Set B2 =a2=$D$4 and copy down 2) Set C2 =C1&IF(B2," “&(ROW()),”") and copy down 3) Write 561674 (or whatever) in D4 and all the row numbers will pop up in column C at the bottom.