Is there a way to do this?

On my boat I have ballast tanks that carry different cargoes.

example: cell A1 to A5 will contain the name of the product. and cell B1 to
B5 will contain amount of barrels in the tank.
lets say the products are ballast water, fresh water, or potable water. in
Cells C1 to C5 I want a formula to track how many bbls of what product is in
the tanks. example: if I enter "fresh water" in A1 and A2 and B1 and B2
contain 100. then a cell in the C row will say "200 bbls fresh water".

I know how to do this using the sumif function but, I want to be able to
change the product names in A1 to A5 to any text and still have the cells in
C row keep track of what that text is and how many bbls are in the cell in
the B row corrosponding to that tank.

And stated different, with sumif I can do =sumif(A1:A5,"fresh water",B1:B5)
for every cell that says "fresh water then the no. value stored in the cell
next to it in the B row will be totaled in the c row and say "200 bbls fresh
water". but I want to be able to change the product name in row A to whatever
I want and still have the C row track it the same way.

I tried to make this clear but I dont know if I did.

I have win xp home and XP office excel 2002.

Any insight would help.

thanks in advance

Greg

=sumif(A1:A5,"fresh water",B1:B5)
you can use
=sumif($A$1:$A$5,A1,$B$1:$B$5)
or you can try SUMPRODUCT
with regards
Sridhar
"Starvoyager" wrote:


Is there a way to do this?

On my boat I have ballast tanks that carry different cargoes.

example: cell A1 to A5 will contain the name of the product. and cell B1 to
B5 will contain amount of barrels in the tank.
lets say the products are ballast water, fresh water, or potable water. in
Cells C1 to C5 I want a formula to track how many bbls of what product is in
the tanks. example: if I enter "fresh water" in A1 and A2 and B1 and B2
contain 100. then a cell in the C row will say "200 bbls fresh water".

I know how to do this using the sumif function but, I want to be able to
change the product names in A1 to A5 to any text and still have the cells in
C row keep track of what that text is and how many bbls are in the cell in
the B row corrosponding to that tank.

And stated different, with sumif I can do =sumif(A1:A5,"fresh water",B1:B5)
for every cell that says "fresh water then the no. value stored in the cell
next to it in the B row will be totaled in the c row and say "200 bbls fresh
water". but I want to be able to change the product name in row A to whatever
I want and still have the C row track it the same way.

I tried to make this clear but I dont know if I did.

I have win xp home and XP office excel 2002.

Any insight would help.

thanks in advance

Greg

Thanks but the product name in cell A1 is not showing up just the amt of
barrels.

In other words if cell A1 contains "fresh water" then I want the totals row
to say "00 bbls fresh water". If I have 5 cells in row A that say "fresh
water" then I want all the bbls added that have "fresh water" in the cell
next to it. So all the bbls of fresh water are totaled in one cell. and for
what ever I change the product name to. Am I doing something wrong or am I
asking too much?

thanks again

Greg

"yshridhar" wrote:

=sumif(A1:A5,"fresh water",B1:B5)
you can use
=sumif($A$1:$A$5,A1,$B$1:$B$5)
or you can try SUMPRODUCT
with regards
Sridhar
"Starvoyager" wrote:


Is there a way to do this?

On my boat I have ballast tanks that carry different cargoes.

example: cell A1 to A5 will contain the name of the product. and cell B1 to
B5 will contain amount of barrels in the tank.
lets say the products are ballast water, fresh water, or potable water. in
Cells C1 to C5 I want a formula to track how many bbls of what product is in
the tanks. example: if I enter "fresh water" in A1 and A2 and B1 and B2
contain 100. then a cell in the C row will say "200 bbls fresh water".

I know how to do this using the sumif function but, I want to be able to
change the product names in A1 to A5 to any text and still have the cells in
C row keep track of what that text is and how many bbls are in the cell in
the B row corrosponding to that tank.

And stated different, with sumif I can do =sumif(A1:A5,"fresh water",B1:B5)
for every cell that says "fresh water then the no. value stored in the cell
next to it in the B row will be totaled in the c row and say "200 bbls fresh
water". but I want to be able to change the product name in row A to whatever
I want and still have the C row track it the same way.

I tried to make this clear but I dont know if I did.

I have win xp home and XP office excel 2002.

Any insight would help.

thanks in advance

Greg

=SUMIF($A$1:$A$5,$A1,$B$1:$B$5)&" bbls "&$A1

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Starvoyager" wrote in message
...
Thanks but the product name in cell A1 is not showing up just the amt of
barrels.

In other words if cell A1 contains "fresh water" then I want the totals
row
to say "00 bbls fresh water". If I have 5 cells in row A that say "fresh
water" then I want all the bbls added that have "fresh water" in the cell
next to it. So all the bbls of fresh water are totaled in one cell. and
for
what ever I change the product name to. Am I doing something wrong or am I
asking too much?

thanks again

Greg

"yshridhar" wrote:

=sumif(A1:A5,"fresh water",B1:B5)
you can use
=sumif($A$1:$A$5,A1,$B$1:$B$5)
or you can try SUMPRODUCT
with regards
Sridhar
"Starvoyager" wrote:


Is there a way to do this?

On my boat I have ballast tanks that carry different cargoes.

example: cell A1 to A5 will contain the name of the product. and cell
B1 to
B5 will contain amount of barrels in the tank.
lets say the products are ballast water, fresh water, or potable water.
in
Cells C1 to C5 I want a formula to track how many bbls of what product
is in
the tanks. example: if I enter "fresh water" in A1 and A2 and B1 and B2
contain 100. then a cell in the C row will say "200 bbls fresh water".

I know how to do this using the sumif function but, I want to be able
to
change the product names in A1 to A5 to any text and still have the
cells in
C row keep track of what that text is and how many bbls are in the cell
in
the B row corrosponding to that tank.

And stated different, with sumif I can do =sumif(A1:A5,"fresh
water",B1:B5)
for every cell that says "fresh water then the no. value stored in the
cell
next to it in the B row will be totaled in the c row and say "200 bbls
fresh
water". but I want to be able to change the product name in row A to
whatever
I want and still have the C row track it the same way.

I tried to make this clear but I dont know if I did.

I have win xp home and XP office excel 2002.

Any insight would help.

thanks in advance

Greg

Another variation to try
Place in C1:
=IF(A1="","",TEXT(SUMIF(A:A,A1,B:B),"#,##0")&" bbls "&A1)
Copy down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Starvoyager" wrote:
Thanks but the product name in cell A1 is not showing up just the amt of
barrels.

In other words if cell A1 contains "fresh water" then I want the totals row
to say "00 bbls fresh water". If I have 5 cells in row A that say "fresh
water" then I want all the bbls added that have "fresh water" in the cell
next to it. So all the bbls of fresh water are totaled in one cell. and for
what ever I change the product name to. Am I doing something wrong or am I
asking too much?

thanks again

Greg

Thanks again for ya'lls time but its not exactly what Im trying to do.

Let me try this.

tank products bbls
totals
A B
C
drill water 10
20 bbls drill water
drill water 10
20 bbls pot water
pot water 10
20 bbls fresh water
pot water 10
fresh water 10
fresh water 10

Now if I change the first tank that says drill water and change it to pot
water the 10 bbls will be subtracted from the drill water total in row C and
added to the pot water total in C. and so on and so forth.

thanks again for ya'lls input

Greg

"Max" wrote:

Another variation to try
Place in C1:
=IF(A1="","",TEXT(SUMIF(A:A,A1,B:B),"#,##0")&" bbls "&A1)
Copy down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Starvoyager" wrote:
Thanks but the product name in cell A1 is not showing up just the amt of
barrels.

In other words if cell A1 contains "fresh water" then I want the totals row
to say "00 bbls fresh water". If I have 5 cells in row A that say "fresh
water" then I want all the bbls added that have "fresh water" in the cell
next to it. So all the bbls of fresh water are totaled in one cell. and for
what ever I change the product name to. Am I doing something wrong or am I
asking too much?

thanks again

Greg

Suggest you set it up this way

Assume source table in cols A and B,
data from row2 down, viz,:

TankProd bbls
drill water 10
drill water 10
pot water 10
etc

List the unique TankProd items in D2:D4, eg:
pot water
drill water
fresh water

Then just place in E2:
=IF(D2="","",TEXT(SUMIF(A:A,D2,B:B),"#,##0")&" bbls "&D2)
Copy E2 down. That should return what you're after. When the source data in
col A is changed (eg pot water replaces drill water in A2), col E will
reflect accordingly.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Starvoyager" wrote:

Thanks again for ya'lls time but its not exactly what Im trying to do.

Let me try this.

tank products bbls
totals
A B
C
drill water 10
20 bbls drill water
drill water 10
20 bbls pot water
pot water 10
20 bbls fresh water
pot water 10
fresh water 10
fresh water 10

Now if I change the first tank that says drill water and change it to pot
water the 10 bbls will be subtracted from the drill water total in row C and
added to the pot water total in C. and so on and so forth.

thanks again for ya'lls input

Greg