Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
=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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
=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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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 "Max" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
"Starvoyager" wrote:
.. 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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" wrote: "Starvoyager" wrote: .. 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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
My earlier clarification was essentially to inform that
your amendment of the front part: =IF($D$2:$D$10="","", is not correct The front part should be retained as: =IF(D2="","", -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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" wrote: My earlier clarification was essentially to inform that your amendment of the front part: =IF($D$2:$D$10="","", is not correct The front part should be retained as: =IF(D2="","", -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif extrapolated
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- .. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |