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

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.

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

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
--
Brian


"Jake" wrote:

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.

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

try this

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

On Jan 30, 9:17*pm, Jake wrote:
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.

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

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

"Brian" wrote in message
...
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
--
Brian


"Jake" wrote:

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.