If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ideas needed, pls help..
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? |
#2
|
|||
|
|||
ideas needed, pls help..
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)*(D27)) 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? |
#3
|
|||
|
|||
ideas needed, pls help..
didn’t 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)*(D27)) 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? |
Thread Tools | |
Display Modes | |
|
|