Mark Petty
Member
Registered: 26th Jul 01
Location: Bournemouth Drives: Suzuki gsf600
User status: Offline
|
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
|
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
|
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
|
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
|
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
|
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
|
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
|
done it now
I needed to do the following:
=SUMIF(A1:A4,"D1",B1:B4)
Thanks
|
