corsasport.co.uk
 

Corsa Sport » Message Board » Off Day » Geek Day » Excel help


New Topic

New Poll
  Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.


Author Excel help
Mark Petty
Member

Registered: 26th Jul 01
Location: Bournemouth Drives: Suzuki gsf600
User status: Offline
18th Jul 05 at 16:31   View User's Profile U2U Member Reply With Quote

Hi

Just wandered whether anyone can help me with an excel problem.
Basically I have a list of country codes and a cost for each country e.g

ColumnA - ColumnB
Country Code- Amount
D1 - 100
D1 - 150
D1 - 112
E1 - 200
E1 - 170

There are hundreds of them, basically I need a sub total for each country code, I reckon I will have to have some new sub total cells but how do I add them all together as the country code changes each month so I don't know how far down the spreadsheet it goes? I need to some how lookup the code and keep adding it if its the same.

Any ideas??

Thanks

[Edited on 18-07-2005 by Mark Petty]
richard_syko
Banned

Registered: 17th Dec 03
Location: Newport, Wales
User status: Offline
18th Jul 05 at 16:36   View User's Profile U2U Member Reply With Quote

Don`t know whether some visual basic program would be any help, not sure excel would do that, but I wouldnt be suprised if it did.
Mark Petty
Member

Registered: 26th Jul 01
Location: Bournemouth Drives: Suzuki gsf600
User status: Offline
18th Jul 05 at 16:39   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by richard_syko
Don`t know whether some visual basic program would be any help, not sure excel would do that, but I wouldnt be suprised if it did.


I could do it in vb but I really need to do it in excel.
Even if I could do some sort of combo box for the country code then it looks up the cost column and adds them up
mark87
Member

Registered: 13th Jul 05
Location: Essex
User status: Offline
18th Jul 05 at 16:44   View User's Profile U2U Member Reply With Quote

I could probably help if I understood what you was asking!

If you want to always look up the cost of a whole column (it'll ignore text characters in the headings) just do the '=sum(B:B)' formula.

If you need to lookup the data use a 'vlookup'.

[Edited on 18-07-2005 by mark87]
Mark Petty
Member

Registered: 26th Jul 01
Location: Bournemouth Drives: Suzuki gsf600
User status: Offline
18th Jul 05 at 16:47   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by mark87
I could probably help if I understood what you was asking!

If you want to always look up the cost of a whole column (it'll ignore text characters in the headings) just do the 'sum=(B:B)' formula.

If you need to lookup the data use a 'vlookup'.


Basically I need to a sum total for each country code, I can't use the sum=(B:B) formula because there could be 100 country codes or more.

What does the VLookup do?
mark87
Member

Registered: 13th Jul 05
Location: Essex
User status: Offline
18th Jul 05 at 16:52   View User's Profile U2U Member Reply With Quote

Will look up data eg.

=vlookup(D1,$A$1:$B$99999,2,FALSE)

The first property is where the value is you are looking up, the second is the table, the next is the column (2 for the B column, for price), and the 4th is, I forgot but set it to false.

Then type in a country code in D1. I think that's it... was a while since I did Excel!

[Edited on 18-07-2005 by mark87]
Mark Petty
Member

Registered: 26th Jul 01
Location: Bournemouth Drives: Suzuki gsf600
User status: Offline
18th Jul 05 at 16:57   View User's Profile U2U Member Reply With Quote

quote:
Originally posted by mark87
Will look up data eg.

=vlookup(D1,$A$1:$B$99999,2,FALSE)

The first property is where the value is you are looking up, the second is the table, the next is the column (2 for the B column, for price), and the 4th is, I forgot but set it to false.

Then type in a country code in D1. I think that's it... was a while since I did Excel!

[Edited on 18-07-2005 by mark87]


Cheers mate I will have a play in a minute thanks
Mark Petty
Member

Registered: 26th Jul 01
Location: Bournemouth Drives: Suzuki gsf600
User status: Offline
18th Jul 05 at 17:41   View User's Profile U2U Member Reply With Quote

done it now

I needed to do the following:
=SUMIF(A1:A4,"D1",B1:B4)

Thanks

 
New Topic

New Poll

  Related Threads Author Forum Replies Views Last Post
Help needed again ...Trotty can you help Red_Corsa Geek Day 17 1673
21st Mar 03 at 00:15
by Red_Corsa
 
koni red suspension kit jam Help Zone, Modification and ICE Advice 5 901
25th Mar 03 at 17:48
by jam
 
Any MS Excel experts Steve Geek Day 11 1288
4th Jun 04 at 14:19
by blebo
 
money making scheme Drew General Chat 520 13805
9th Apr 09 at 16:51
by Mark Prydderch
 

Corsa Sport » Message Board » Off Day » Geek Day » Excel help 29 database queries in 0.0150611 seconds