Suppose I have a spreadsheet with the following data

Column A B C D
Country Company Profit No. of Employees
Hong Kong ABC 1000 10
Hong Kong XYZ 2000 20
UK XXX 1100 14
UK YYY 2500 15
Singapore AAA 3000 30
Australia CCC 1000 20

The data will be collected and updated on a quarterly basis
My task is to design a user-friendly spreadsheet, establish a clear set of
rules/procedures for on-going updates purpose in order to track our portfolio
size

Therefore, some of the data will be changed, and some won't .

I have now done a summary table with formulas as follows
Profit No. of employees
Hong Kong
UK
Singapore
Australia

Do you have any ideas how I can improve my spreadsheet?

Hi,

Copy your countries to another place which in my case I've put Hong Kong in
A14, then use this formula to return profit
=SUMPRODUCT((A2:A7=A14)*(C2:C7))

and this to return employees
=SUMPRODUCT((A2:A7=A14)*(D2:D7))

I have assumed your data table is in A1 to D7.

Mike
"angel" wrote:

Suppose I have a spreadsheet with the following data

Column A B C D
Country Company Profit No. of Employees
Hong Kong ABC 1000 10
Hong Kong XYZ 2000 20
UK XXX 1100 14
UK YYY 2500 15
Singapore AAA 3000 30
Australia CCC 1000 20

The data will be collected and updated on a quarterly basis
My task is to design a user-friendly spreadsheet, establish a clear set of
rules/procedures for on-going updates purpose in order to track our portfolio
size

Therefore, some of the data will be changed, and some won't .

I have now done a summary table with formulas as follows
Profit No. of employees
Hong Kong
UK
Singapore
Australia

Do you have any ideas how I can improve my spreadsheet?

didnt get exactly what the results
you want to compare the profit & no of employess countrywise??

if you are okay with mike's suggestion. go on you can try this also
SUMIF(range,criteria,sum_range)


"Mike H" wrote in message
...
Hi,

Copy your countries to another place which in my case I've put Hong Kong
in
A14, then use this formula to return profit
=SUMPRODUCT((A2:A7=A14)*(C2:C7))

and this to return employees
=SUMPRODUCT((A2:A7=A14)*(D2:D7))

I have assumed your data table is in A1 to D7.

Mike
"angel" wrote:

Suppose I have a spreadsheet with the following data

Column A B C D
Country Company Profit No. of Employees
Hong Kong ABC 1000 10
Hong Kong XYZ 2000 20
UK XXX 1100 14
UK YYY 2500 15
Singapore AAA 3000 30
Australia CCC 1000 20

The data will be collected and updated on a quarterly basis
My task is to design a user-friendly spreadsheet, establish a clear set
of
rules/procedures for on-going updates purpose in order to track our
portfolio
size

Therefore, some of the data will be changed, and some won't .

I have now done a summary table with formulas as follows
Profit No. of employees
Hong Kong
UK
Singapore
Australia

Do you have any ideas how I can improve my spreadsheet?