| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Seeking Countdown Formula | Excel Worksheet Functions | |||
| Desperately Seeking Offset Formula!! | Excel Discussion (Misc queries) | |||
| Seeking someone to look over my work | Excel Discussion (Misc queries) | |||
| seeking of array | Excel Worksheet Functions | |||
| Seeking help from a GURU | Excel Worksheet Functions | |||
Linear Mode
