| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum rows if muliple cells meet criteria but not others
I have the following set of data in a spreadsheet. I'm looking for a formula
that will sum the price of 2 carrier sectors. For example, lines one and two are in sector X of Asset 860306, so I'd want the formula to search and add the price column for those together. Likewise, I'd want lines 3 and 4 to sum because they are the two carriers on sector Y of asset 860306. However, when you get to lines 7, 8 and 9, asset 860303 only has one carrier on each sector so I would just want to capture the price of each individual sector. It's a long list. Any thoughts on how to do this programmatically? Fullkey Sectkey Name Asset Number Sector Carrier Price 1005-X-1 1005-X BAX 860306 1005 X 1 2.41 1005-X-2 1005-X BAX 860306 1005 X 2 1.671 1005-Y-1 1005-Y BAX 860306 1005 Y 1 1.621 1005-Y-2 1005-Y BAX 860306 1005 Y 2 1.764 1005-Z-1 1005-Z BAX 860306 1005 Z 1 3.561 1005-Z-2 1005-Z BAX 860306 1005 Z 2 0.347 1002-X-1 1002-X BLKM 860303 1002 X 1 2.492 1002-Y-1 1002-Y BLKM 860303 1002 Y 1 3.036 1002-Z-1 1002-Z BLKM 860303 1002 Z 1 6.375 1068-X-1 1068-X BLO 860364 1068 X 1 2.758 1068-Y-1 1068-Y BLO 860364 1068 Y 1 2.577 1068-Z-1 1068-Z BLO 860364 1068 Z 1 3.249 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum rows if muliple cells meet criteria but not others
Depending on your data you could probably get a pivot table to work. Select
the data and choose Data - Pivot Table & Pivot chart When the wizard pops up just select finish (that will ususally work just fine) Drag the Asset and Sector into the left column of the table and the price into the center data section. -- HTH... Jim Thomlinson "Amy" wrote: I have the following set of data in a spreadsheet. I'm looking for a formula that will sum the price of 2 carrier sectors. For example, lines one and two are in sector X of Asset 860306, so I'd want the formula to search and add the price column for those together. Likewise, I'd want lines 3 and 4 to sum because they are the two carriers on sector Y of asset 860306. However, when you get to lines 7, 8 and 9, asset 860303 only has one carrier on each sector so I would just want to capture the price of each individual sector. It's a long list. Any thoughts on how to do this programmatically? Fullkey Sectkey Name Asset Number Sector Carrier Price 1005-X-1 1005-X BAX 860306 1005 X 1 2.41 1005-X-2 1005-X BAX 860306 1005 X 2 1.671 1005-Y-1 1005-Y BAX 860306 1005 Y 1 1.621 1005-Y-2 1005-Y BAX 860306 1005 Y 2 1.764 1005-Z-1 1005-Z BAX 860306 1005 Z 1 3.561 1005-Z-2 1005-Z BAX 860306 1005 Z 2 0.347 1002-X-1 1002-X BLKM 860303 1002 X 1 2.492 1002-Y-1 1002-Y BLKM 860303 1002 Y 1 3.036 1002-Z-1 1002-Z BLKM 860303 1002 Z 1 6.375 1068-X-1 1068-X BLO 860364 1068 X 1 2.758 1068-Y-1 1068-Y BLO 860364 1068 Y 1 2.577 1068-Z-1 1068-Z BLO 860364 1068 Z 1 3.249 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum rows if muliple cells meet criteria but not others
Could the below be an acceptable result that you're after?
------------- Sum of Price Name Asset Number Sector Total BAX 860306 1005 X 4.081 Z 3.908 Y 3.385 BLKM 860303 1002 X 2.492 Z 6.375 Y 3.036 BLO 860364 1068 X 2.758 Z 3.249 Y 2.577 ------------------ If so, just create a pivot, in Layout, 1. Place the fields: Name, Asset, Number, Sector into the ROW area (one below the other) then double-click on each field, set Subtotals to none 2. Place Price into the DATA area (it'll show as Sum) Click to finish, that's it. The above result will appear in the pivot sheet. Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Amy" wrote: I have the following set of data in a spreadsheet. I'm looking for a formula that will sum the price of 2 carrier sectors. For example, lines one and two are in sector X of Asset 860306, so I'd want the formula to search and add the price column for those together. Likewise, I'd want lines 3 and 4 to sum because they are the two carriers on sector Y of asset 860306. However, when you get to lines 7, 8 and 9, asset 860303 only has one carrier on each sector so I would just want to capture the price of each individual sector. It's a long list. Any thoughts on how to do this programmatically? Fullkey Sectkey Name Asset Number Sector Carrier Price 1005-X-1 1005-X BAX 860306 1005 X 1 2.41 1005-X-2 1005-X BAX 860306 1005 X 2 1.671 1005-Y-1 1005-Y BAX 860306 1005 Y 1 1.621 1005-Y-2 1005-Y BAX 860306 1005 Y 2 1.764 1005-Z-1 1005-Z BAX 860306 1005 Z 1 3.561 1005-Z-2 1005-Z BAX 860306 1005 Z 2 0.347 1002-X-1 1002-X BLKM 860303 1002 X 1 2.492 1002-Y-1 1002-Y BLKM 860303 1002 Y 1 3.036 1002-Z-1 1002-Z BLKM 860303 1002 Z 1 6.375 1068-X-1 1068-X BLO 860364 1068 X 1 2.758 1068-Y-1 1068-Y BLO 860364 1068 Y 1 2.577 1068-Z-1 1068-Z BLO 860364 1068 Z 1 3.249 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum rows if muliple cells meet criteria but not others
I'm an idiot! Pivot didn't even occur to me. Works great! Thanks guys!
"Max" wrote: Could the below be an acceptable result that you're after? ------------- Sum of Price Name Asset Number Sector Total BAX 860306 1005 X 4.081 Z 3.908 Y 3.385 BLKM 860303 1002 X 2.492 Z 6.375 Y 3.036 BLO 860364 1068 X 2.758 Z 3.249 Y 2.577 ------------------ If so, just create a pivot, in Layout, 1. Place the fields: Name, Asset, Number, Sector into the ROW area (one below the other) then double-click on each field, set Subtotals to none 2. Place Price into the DATA area (it'll show as Sum) Click to finish, that's it. The above result will appear in the pivot sheet. Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Amy" wrote: I have the following set of data in a spreadsheet. I'm looking for a formula that will sum the price of 2 carrier sectors. For example, lines one and two are in sector X of Asset 860306, so I'd want the formula to search and add the price column for those together. Likewise, I'd want lines 3 and 4 to sum because they are the two carriers on sector Y of asset 860306. However, when you get to lines 7, 8 and 9, asset 860303 only has one carrier on each sector so I would just want to capture the price of each individual sector. It's a long list. Any thoughts on how to do this programmatically? Fullkey Sectkey Name Asset Number Sector Carrier Price 1005-X-1 1005-X BAX 860306 1005 X 1 2.41 1005-X-2 1005-X BAX 860306 1005 X 2 1.671 1005-Y-1 1005-Y BAX 860306 1005 Y 1 1.621 1005-Y-2 1005-Y BAX 860306 1005 Y 2 1.764 1005-Z-1 1005-Z BAX 860306 1005 Z 1 3.561 1005-Z-2 1005-Z BAX 860306 1005 Z 2 0.347 1002-X-1 1002-X BLKM 860303 1002 X 1 2.492 1002-Y-1 1002-Y BLKM 860303 1002 Y 1 3.036 1002-Z-1 1002-Z BLKM 860303 1002 Z 1 6.375 1068-X-1 1068-X BLO 860364 1068 X 1 2.758 1068-Y-1 1068-Y BLO 860364 1068 Y 1 2.577 1068-Z-1 1068-Z BLO 860364 1068 Z 1 3.249 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum rows if muliple cells meet criteria but not others
Glad to hear. Our delights.
-- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Amy" wrote in message ... I'm an idiot! Pivot didn't even occur to me. Works great! Thanks guys! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel formular to Count values in cells of rows that meet criteria | Excel Worksheet Functions | |||
| Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
| Selecting Rows that meet criteria | Excel Worksheet Functions | |||
| count rows that meet certain criteria | Excel Discussion (Misc queries) | |||
| Delete Rows where cells does not meet criteria | Excel Worksheet Functions | |||
Linear Mode
