Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
if statement using rows and columns
i have a chart that auto-populates Mat'l, Diameter, and length.
the mat'l can be mat'l1, matl2, or matl3 the diameter can be 1,1.5,2,2.5....all the way to 10 how can i calculate the total length of each matl at each diameter? my chart looks like Mat'l | Diameter | Length 1 1 10 1 1 175 2 1.5 55 and so on. so i want to show Matl1 ø1 has a total length of 175 Matl2 ø1.5 has a total length of 55 and so on. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
if statement using rows and columns
Assuming:
The data below is in columns A - C of Sheet1, and the list is 500 rows down including headers in row 1. You have Matl1 and 1 in a table on a separate sheet in columns A - B with result to be posted in column C =SUMPRODUCT(--(Sheet1!$A$2:$A$500=$A2),--(Sheet1!$B$2:$B4500=$B2),$C$2:$C$500) "Joe@Phoenix" wrote: i have a chart that auto-populates Mat'l, Diameter, and length. the mat'l can be mat'l1, matl2, or matl3 the diameter can be 1,1.5,2,2.5....all the way to 10 how can i calculate the total length of each matl at each diameter? my chart looks like Mat'l | Diameter | Length 1 1 10 1 1 175 2 1.5 55 and so on. so i want to show Matl1 ø1 has a total length of 175 Matl2 ø1.5 has a total length of 55 and so on. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
if statement using rows and columns
1) do you input material, and then both diameter and length get input?
or do you input both material and diameter? 2) Is length dependent on just diameter, or both diameter and material? 3) Is there a specific formula for calculating length, or is it just corresponding to the set diameters? If it is corresponding to set diameters, do you mind having a separate chart which looks up the values, or do you want it all done through formulas? "Joe@Phoenix" wrote: i have a chart that auto-populates Mat'l, Diameter, and length. the mat'l can be mat'l1, matl2, or matl3 the diameter can be 1,1.5,2,2.5....all the way to 10 how can i calculate the total length of each matl at each diameter? my chart looks like Mat'l | Diameter | Length 1 1 10 1 1 175 2 1.5 55 and so on. so i want to show Matl1 ø1 has a total length of 175 Matl2 ø1.5 has a total length of 55 and so on. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
if statement using rows and columns
ok this is what im working with
i have a 'tooling order form' and it looks like this Shop Order Number | Part Name | Matl | ø | Matl Length | bunch of other stuff not important i have a 'Tools Needed' form that gets sent out to the shop to be made and looks like this Sheet1 Shop Order Number | Part Name | bunch of other stuff not important Sheet2 is my chart for the matl. i pull the Shop Order Number from sheet1 and then i vlookup the matl, ø, and length from 'Tooling Order Form' so im trying to generate the total lengths of the different ø of matl i need to order to make the tools. Ex: Matl1 - ø1" - Total 500mm needed Matl1 - ø1.5" - Total 150mm needed and so on and so on the 'Tools Needed' sheet1 gets printed and sent to the shop in an order for them to make. so i cant sort that by matl and ø. "Sean Timmons" wrote: Assuming: The data below is in columns A - C of Sheet1, and the list is 500 rows down including headers in row 1. You have Matl1 and 1 in a table on a separate sheet in columns A - B with result to be posted in column C =SUMPRODUCT(--(Sheet1!$A$2:$A$500=$A2),--(Sheet1!$B$2:$B4500=$B2),$C$2:$C$500) "Joe@Phoenix" wrote: i have a chart that auto-populates Mat'l, Diameter, and length. the mat'l can be mat'l1, matl2, or matl3 the diameter can be 1,1.5,2,2.5....all the way to 10 how can i calculate the total length of each matl at each diameter? my chart looks like Mat'l | Diameter | Length 1 1 10 1 1 175 2 1.5 55 and so on. so i want to show Matl1 ø1 has a total length of 175 Matl2 ø1.5 has a total length of 55 and so on. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
if statement using rows and columns
1) the matl, dia and length get inputed seperatly
2) both dia and matl 3) it goes by the matl and dia, i dont really have a preference on how it gets done as long as it doesnt screw up my sheet1. (see my previous reply) "Derrick" wrote: 1) do you input material, and then both diameter and length get input? or do you input both material and diameter? 2) Is length dependent on just diameter, or both diameter and material? 3) Is there a specific formula for calculating length, or is it just corresponding to the set diameters? If it is corresponding to set diameters, do you mind having a separate chart which looks up the values, or do you want it all done through formulas? "Joe@Phoenix" wrote: i have a chart that auto-populates Mat'l, Diameter, and length. the mat'l can be mat'l1, matl2, or matl3 the diameter can be 1,1.5,2,2.5....all the way to 10 how can i calculate the total length of each matl at each diameter? my chart looks like Mat'l | Diameter | Length 1 1 10 1 1 175 2 1.5 55 and so on. so i want to show Matl1 ø1 has a total length of 175 Matl2 ø1.5 has a total length of 55 and so on. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
if statement using rows and columns
May just be easy enough to use a Pivot table...
Highlight the Mat'l, diam and length columns, Data Pivot Table... Finish Drop Mat'l and Diam into the Row fields section and length into the Data section. Right click in the data section, select Field Settings and ensure it is calculating Sum, not Count. This will show each Mat'l and diam with total length for each specific diam, each Mat'l and a grand total for the entire order. "Joe@Phoenix" wrote: ok this is what im working with i have a 'tooling order form' and it looks like this Shop Order Number | Part Name | Matl | ø | Matl Length | bunch of other stuff not important i have a 'Tools Needed' form that gets sent out to the shop to be made and looks like this Sheet1 Shop Order Number | Part Name | bunch of other stuff not important Sheet2 is my chart for the matl. i pull the Shop Order Number from sheet1 and then i vlookup the matl, ø, and length from 'Tooling Order Form' so im trying to generate the total lengths of the different ø of matl i need to order to make the tools. Ex: Matl1 - ø1" - Total 500mm needed Matl1 - ø1.5" - Total 150mm needed and so on and so on the 'Tools Needed' sheet1 gets printed and sent to the shop in an order for them to make. so i cant sort that by matl and ø. "Sean Timmons" wrote: Assuming: The data below is in columns A - C of Sheet1, and the list is 500 rows down including headers in row 1. You have Matl1 and 1 in a table on a separate sheet in columns A - B with result to be posted in column C =SUMPRODUCT(--(Sheet1!$A$2:$A$500=$A2),--(Sheet1!$B$2:$B4500=$B2),$C$2:$C$500) "Joe@Phoenix" wrote: i have a chart that auto-populates Mat'l, Diameter, and length. the mat'l can be mat'l1, matl2, or matl3 the diameter can be 1,1.5,2,2.5....all the way to 10 how can i calculate the total length of each matl at each diameter? my chart looks like Mat'l | Diameter | Length 1 1 10 1 1 175 2 1.5 55 and so on. so i want to show Matl1 ø1 has a total length of 175 Matl2 ø1.5 has a total length of 55 and so on. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i paste rows/columns avoiding hidden rows/columns | Excel Discussion (Misc queries) | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
how to interchange rows to columns & columns to rows in a table | Excel Discussion (Misc queries) |