How do I sum numbers that match two conditions?

[Guest]

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

 

[Bernard Liengme]

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

 

[Guest]

Hi Bernard, Thanks for your help. I managed to figure out one that works
(below), but your formula looks simpler than the one that I came up with.
What's a pivot table?

{=SUM(IF($A$2:$A$10000=E$1,IF($B$2:$B$10000=$D2,$C$2:$C$10000,0),0))}

(You have to press Ctrl-Shift-Enter to get the {} around the formula or it
doesn't work...)

My formula above is based on the following...

Data Table:
Column A is Type
Column B is Diameter
Column C is Length

Summary Table:
E1 is Type to match
D2 is Diameter to match