Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hightlighting Numbers & then all Cells to the right of these Numbers. | Excel Worksheet Functions | |||
Match numbers in range | Excel Discussion (Misc queries) | |||
Match Function Problem - Won't Find Certain Numbers | Excel Discussion (Misc queries) | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |