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

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

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

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

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!