vba - sum based on cell values 1

[darkdestroyer1980]

Hi,

I have an Excel spreadsheet in the format below. I'd like to write a formula that can calculate the trader commission total, as well as the broker commission within each trader. Essentially, it'll add the commission columns for each value relating to trader1 to give an accumulative. It will also add the commission totals for lloyds & hsbc for trader 1.

Then do this for the rest of the traders.
Any ideas on how to do this much appreciated.

Thanks

broker commission Trader

lloyds 3,432 trader1
lloyds 5,433 trader1
lloyds 21,231 trader1
hsbc 4,204 trader1
hsbc 16,884 trader1
hsbc 43,343 trader1
hsbc 4,563 trader1
hsbc 10,000 trader1
abbey 5,500 trader1
abbey 2,323 trader2
abbey 324,234 trader2
lloyds 5,169 trader2
lloyds 544 trader2
lloyds 5,169 trader2
hsbc 5,169 trader2
hsbc 34,343 trader2
hsbc 5,500 trader2
hsbc 5,169 trader2
hsbc 10,000 trader2
abbey 5,500 trader2
abbey 11,550 trader2
abbey 34,343 trader 3
lloyds 10,000 trader 3
lloyds 10,000 trader 3
lloyds 34,343 trader 3
hsbc 10,000 trader 3
hsbc 34,343 trader 3
northern rock 10,000 trader 3
northern rock 34,343 trader 3

 

[xlbo]

VBA ??????

pivot table pivot table pivot table pivot table.......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Check out the SUMPRODUCT function using Named Ranges...
[tt]
=SUMPRODUCT((Trader="trader1")*(Broker="hsbc")*(commission))
[/tt]


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[darkdestroyer1980]

thx, pivot table worked a treat