Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Won't Work after 10 Cells | Excel Discussion (Misc queries) | |||
Formula for sorting a range of cells in decending order | Excel Discussion (Misc queries) | |||
Sorting and Eliminating Blank Cells in Formula range | Excel Worksheet Functions | |||
How can I get a formula to work on visible cells with autofilter? | Excel Discussion (Misc queries) | |||
in excel, formula to work only when all cells full of data | New Users to Excel |