How do I sum numbers that match two conditions?
 

Hi All:
I have three columns, Material, Diameter, and Length. There are about 7000
rows of data. I need to sum particular lengths that match specific material
types and diameters.
eg.
A B C (Length
Summary Table)
Material Diameter Length Diameter PVC AC

PVC 100 1200 100 2500
1000
PVC 100 1300 150 500
-
AC 100 1000
PVC 150 500

I need a formula that can be used in the summary table to sum all lengths
that match the Material and Diameter, eg. a total length of 2500 for 100
diameter PVC.

Thanks,
Ryan

How do I sum numbers that match two conditions?
 

Assuming your table is in A2:A7001
In D1 you have the 'type' to be found, in D2 the diameter to find
=SUMPRODUCT(--(A2:A7001=D1),--(B2:B7001=D2),C2:C7001)
Remember to make reference to table columns absolute if you car going to
copy the formula.
The double unitary negation converts FALSE/TRUE to 0/1 so arithmetic can be
done

Have you thought about using a Pivot Table?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"ryesworld" wrote in message
...
Hi All:
I have three columns, Material, Diameter, and Length. There are about
7000
rows of data. I need to sum particular lengths that match specific
material
types and diameters.
eg.
A B C (Length
Summary Table)
Material Diameter Length Diameter PVC
AC

PVC 100 1200 100
2500
1000
PVC 100 1300 150
500
-
AC 100 1000
PVC 150 500

I need a formula that can be used in the summary table to sum all lengths
that match the Material and Diameter, eg. a total length of 2500 for 100
diameter PVC.

Thanks,
Ryan