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.

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.

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.

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.

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.

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.