Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
A B
1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
Using your example:
This formula returns the values associated with "Car": B6: =SUMIF($A$1:$A$5,"Car",$B$1:$B$5) Or...for more flexibility: A6: Car B6: =SUMIF($A$1:$A$5,A6,$B$1:$B$5) Change range references to suit your situation. Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "FC" wrote in message ... A B 1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
=SUMIF(A1:A5,"car",B1:B5)
"FC" wrote: A B 1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
That is exactly my needed formula. Thanks a lot Mike, appreciated.
"Mike" wrote: =SUMIF(A1:A5,"car",B1:B5) "FC" wrote: A B 1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
Here's one:
=100*SUMIF(A1:A5,"car",B1:B5) You could put "car" into another cell, eg A6, then the formula could become: =100*SUMIF(A$1:A$5,A6,B$1:B$5) I've made the row references absolute, so that you could put, say, "boat" in A7 and copy the formula into B7 etc. Hope this helps. Pete On Sep 28, 1:01 am, FC wrote: A B 1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
Thanks Ron, keep the good work.
"Ron Coderre" wrote: Using your example: This formula returns the values associated with "Car": B6: =SUMIF($A$1:$A$5,"Car",$B$1:$B$5) Or...for more flexibility: A6: Car B6: =SUMIF($A$1:$A$5,A6,$B$1:$B$5) Change range references to suit your situation. Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "FC" wrote in message ... A B 1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
On Thu, 27 Sep 2007 17:01:00 -0700, FC wrote:
A B 1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance. In addition to the SUMIF formula others have recommended, you might consider using a Pivot Table (see the Data menu). Using your data, I get this as a result: Item Sum of Items airp 22 boat 45 car 100 Grand Total 167 but it can be formatted in a variety of ways. --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
THANKS A LOT.
"Pete_UK" wrote: Here's one: =100*SUMIF(A1:A5,"car",B1:B5) You could put "car" into another cell, eg A6, then the formula could become: =100*SUMIF(A$1:A$5,A6,B$1:B$5) I've made the row references absolute, so that you could put, say, "boat" in A7 and copy the formula into B7 etc. Hope this helps. Pete On Sep 28, 1:01 am, FC wrote: A B 1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
You're welcome.
Pete On Sep 28, 2:21 am, FC wrote: THANKS A LOT. "Pete_UK" wrote: Here's one: =100*SUMIF(A1:A5,"car",B1:B5) You could put "car" into another cell, eg A6, then the formula could become: =100*SUMIF(A$1:A$5,A6,B$1:B$5) I've made the row references absolute, so that you could put, say, "boat" in A7 and copy the formula into B7 etc. Hope this helps. Pete On Sep 28, 1:01 am, FC wrote: A B 1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance.- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula needed.
i have the same question, would you be able to send me the working example
also. Please, many thanks. to "Ron Rosenfeld" wrote: On Thu, 27 Sep 2007 17:01:00 -0700, FC wrote: A B 1 car 90 2 boat 20 3 airp 22 4 car 10 5 boat 25 I'm needing a formula that will add numbers in B related to the item in A . Let's say a result for car = 100 ( 90+10) at the bottom on B6. These are just 5 rows but I have around 150, however if you kindly provide me with a formula for those 5, I can adjust it. THANKS in advance. In addition to the SUMIF formula others have recommended, you might consider using a Pivot Table (see the Data menu). Using your data, I get this as a result: Item Sum of Items airp 22 boat 45 car 100 Grand Total 167 but it can be formatted in a variety of ways. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FORMULA NEEDED | Excel Discussion (Misc queries) | |||
Formula needed - is this possible? | Excel Discussion (Misc queries) | |||
formula needed | Excel Worksheet Functions | |||
Formula Help Needed | Excel Worksheet Functions | |||
Formula needed | Excel Worksheet Functions |