#1   Report Post  
Posted to microsoft.public.excel.misc
FC FC is offline
external usenet poster
 
Posts: 130
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
FC FC is offline
external usenet poster
 
Posts: 130
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
FC FC is offline
external usenet poster
 
Posts: 130
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
FC FC is offline
external usenet poster
 
Posts: 130
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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 -



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
FORMULA NEEDED Kerri Olsen Excel Discussion (Misc queries) 5 July 16th 07 08:12 PM
Formula needed - is this possible? Gary''s Student Excel Discussion (Misc queries) 0 March 28th 07 01:06 AM
formula needed ah Excel Worksheet Functions 9 February 5th 07 08:38 AM
Formula Help Needed FordFiestaST150 Excel Worksheet Functions 1 November 3rd 06 09:41 AM
Formula needed Admiral70 Excel Worksheet Functions 4 August 18th 05 09:58 PM


All times are GMT +1. The time now is 05:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"