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

[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.

 

[Brian]

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

 

[muddan madhu]

try this

=SUBTOTAL(9,B2,B12,B15,B20,B35)

 

[Ashish Mathur]

Hi,

You would not need to use the Ctrl+Shift+Enter if you are using SUMPRODUCT.
Just hit the Enter key

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com