Sorting the cells of a formula causes the formula to not work

  • Thread starter Thread starter Jake
  • Start date Start date
J

Jake

I have a list of parts (about 50) in colomn A and thier cost in column B.
Below this data set I have a formula to sum the purchased parts, another
formula to sum the welded parts, and another formula to sum the labor
intensive parts. An example of these formulas would be
"=sum(B2,B12,B15,B20,B35)". When I sort the list of parts alphabeticaly or
the cost low to high / high to low the formulas below do not track the sorted
cells. It still references the same thing (ie. "=sum(B2,B12,B15,B20,B35)")
even though the sorted cells have moved positions. Please help.
 
Jake,

Add a third column C and label it Part Type. In this column put either
Purchased, Welded, or Labor Intensive for all the parts. To get the Total of
Purchased Parts use this:

=SUMPRODUCT(($B$2:$B$50)*($C$2:$C$50="Purchased"))

use Ctrl-Shft-Enter when inputting this formula. To get the Welded and
Labor Intensive, just replace Purchased with either Welded or Labor Intensive

Hope this helps
 
Back
Top