seeking help
 

Suppose I have the following data:
Column A Column B
Hong Kong 200
Hong Kong 300
Hong Kong 400
Australia 200
Australia 300
UK 100
France 400


I would like to make a summary in the bottom of the spreadsheet like
HK xx
Australia xx
UK xx
France xx

Therefore, I would like to set some formula in which i can calculate the
total sum of Hong Kong (eg, 200+300+400), Australia, UK, France separately
and automatically

I've tried the lookup functions but i cant do so since there are duplicate
data. can anyone advise me on this? thanks.

seeking help
 

This will sum up only the Hong Kong numbers in your list from Row 1 to Row
7...

=SUMPRODUCT((A1:A7="Hong Kong")*(B1:B7))

Adjust the the two 7's to the last row number containing data. For the other
locations, change the name "Hong Kong" to the location name you want the
totals for.

Rick


"angel" wrote in message
...
Suppose I have the following data:
Column A Column B
Hong Kong 200
Hong Kong 300
Hong Kong 400
Australia 200
Australia 300
UK 100
France 400


I would like to make a summary in the bottom of the spreadsheet like
HK xx
Australia xx
UK xx
France xx

Therefore, I would like to set some formula in which i can calculate the
total sum of Hong Kong (eg, 200+300+400), Australia, UK, France separately
and automatically

I've tried the lookup functions but i cant do so since there are duplicate
data. can anyone advise me on this? thanks.

seeking help
 

perfect, thanks.

"Rick Rothstein (MVP - VB)" wrote:

This will sum up only the Hong Kong numbers in your list from Row 1 to Row
7...

=SUMPRODUCT((A1:A7="Hong Kong")*(B1:B7))

Adjust the the two 7's to the last row number containing data. For the other
locations, change the name "Hong Kong" to the location name you want the
totals for.

Rick


"angel" wrote in message
...
Suppose I have the following data:
Column A Column B
Hong Kong 200
Hong Kong 300
Hong Kong 400
Australia 200
Australia 300
UK 100
France 400


I would like to make a summary in the bottom of the spreadsheet like
HK xx
Australia xx
UK xx
France xx

Therefore, I would like to set some formula in which i can calculate the
total sum of Hong Kong (eg, 200+300+400), Australia, UK, France separately
and automatically

I've tried the lookup functions but i cant do so since there are duplicate
data. can anyone advise me on this? thanks.