Sumif extrapolated

[Starvoyager]

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

 

[yshridhar]

=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]

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

 

[Bob Phillips]

=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)

 

[Max]

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

 

[Starvoyager]

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]

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 D24, 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.

 

[Starvoyager]

Thanks more like what Im trying to do. I tried to represent the cell table in
my last message but the spacing got screwed up. this is what I wanted you all
to see if it turns out ok. anyway thanks again. I'll have to add that formula
to my database for future referance.

A B C
drill w 10 20 bbls drill w
drill w 10 20 bbls pot w
pot w 10 20 bbls fresh w
pot w 10
fresh w 10
fresh w 10

 

[Starvoyager]

Ok I was going to ask another question but I figured it out with ya'lls help.
Below is what I did. I wanted to limit the range of cells for the tank
products and this works. thanks

Greg

=IF($D$2:$D$10="","",TEXT(SUMIF($A$2:$A$10,D2,$B$2:$B$10),"#,##0")&" BBLS
"&D2)

 

[Max]

.. I wanted to limit the range of cells for the tank
products and this works ...

=IF($D$2:$D$10="","",TEXT(SUMIF($A$2:$A$10,D2,$B$2:$B$10),"#,##0")&" BBLS
"&D2)

The IF check on col D should actually be pointing to single cells,
viz. suggest you change it to this, and copy down:
=IF(D2="","",TEXT(SUMIF($A$2:$A$10,D2,$B$2:$B$10),"#,##0")&" BBLS "&D2)

---

 

[Starvoyager]

OK, but I need to limit the range cause I dont need it to extend down forever.
I will have other data underneath it. like other types of tanks with
differant products.
Right now it seems to be doing what I wanted. The reason why Im trying to
figure it out is the boats I run carry all differant kinds of cargo's and
sometimes we will load something that I dont have in my workbook. So I would
have to re-do the formulas to add them in and sometimes I can't even remember
how I did it.

with what I trying now I would just have to add the new item to the named
range.

Greg

 

[Max]

My earlier clarification was essentially to inform that
your amendment of the front part:is not correct

The front part should be retained as:
---

 

[Starvoyager]

OK Max, but why is it not correct? it seems to be doing what I was looking for.
The method that your telling me to use extends the row all the way down for
infinity doesn't it? But I only want it to extend down about 10 columns.
Which is what my incorrect formula is doing. So tell me why is it not
correct? Im asking so maybe I can learn something else.

thanks Greg

 

[Max]

it seems to be doing what I was looking for...

=IF($D$2:$D$10="","",

is redundant in a formula which is normally entered in a single cell. It
actually returns a resulting array of TRUEs/FALSEs while what is needed is
just a simple, direct check on the corresponding cell in col D, viz the
suggested:

=IF(D2="","",

The method that your telling me to use
extends the row all the way down for infinity doesn't it?

No, you've got it confused with the SUMIF. I didn't say your amendment of
the SUMIF range to be a fixed range was incorrect.