Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database function that sums?
Can anyone assist me with this please, I have a spread sheet in excel in the
following format, however I am now required to present each individuals basic salary and just the sum of their allowances, instead of each allowance being itemized. I am not sure how I am to accomplish this seeing that each employee has different allowances. Could anyone help? Appreciated. Surname Basic Salary Description Allowances ABRAHAM $6,190.00 ACTING ALLOW $1,581.00 ABRAHAM $6,190.00 LIVING OUT $100.00 ABRAHAM $6,190.00 NURSES INCT $1,547.50 ABRAHAM $6,190.00 MEAL ALLOWANCE $525.00 ABRAHAM $6,190.00 LAUNDRY ALLOWANCE $190.00 ABRAHAM $6,190.00 ARREARS INCENTIVE $3,814.38 ABRAHIM $2,962.00 OT - 2.0 $273.42 ABRAHIM $2,962.00 MEAL ALLOWANCE $525.00 ABRAHIM $2,962.00 LAUNDRY ALLOWANCE $190.00 ABRAHIM $2,962.00 COLA $60.00 ABRAHIM $2,962.00 SHIFT ALLOW $192.00 ABRAHIM $12,000.00 COMM ALLOW $500.00 ABRAHIM $12,000.00 COVERAGE ALLOW $2,300.00 ABRAHIM $12,000.00 SERVICE PREMIUM $500.00 ABRAHIM $12,000.00 TRAVEL (NON TAXED) $1,000.00 SUTTON $12,000.00 OVERTIME $2,307.60 SUTTON $12,000.00 TRANSPORT ALLOW $1,300.00 SUTTON $12,000.00 CONT'D EDU ALLOW $1,880.00 SUTTON $12,000.00 ACTING ALLOW $2,481.60 SUTTON $12,000.00 ON CALL ALLOW $6,600.00 SUTTON $12,000.00 HEAD OF DEP'T ALLOW $1,560.00 SUTTON $12,000.00 ACTING ALLOW $1,581.60 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database function that sums?
Salary:
=INDEX(B:B,MATCH("ABRAHAM",B:B,0)) Allowances =SUMIF(A:A,"ABRAHAM",D:D) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Keensie" wrote in message ... Can anyone assist me with this please, I have a spread sheet in excel in the following format, however I am now required to present each individual's basic salary and just the sum of their allowances, instead of each allowance being itemized. I am not sure how I am to accomplish this seeing that each employee has different allowances. Could anyone help? Appreciated. Surname Basic Salary Description Allowances ABRAHAM $6,190.00 ACTING ALLOW $1,581.00 ABRAHAM $6,190.00 LIVING OUT $100.00 ABRAHAM $6,190.00 NURSES INCT $1,547.50 ABRAHAM $6,190.00 MEAL ALLOWANCE $525.00 ABRAHAM $6,190.00 LAUNDRY ALLOWANCE $190.00 ABRAHAM $6,190.00 ARREARS INCENTIVE $3,814.38 ABRAHIM $2,962.00 OT - 2.0 $273.42 ABRAHIM $2,962.00 MEAL ALLOWANCE $525.00 ABRAHIM $2,962.00 LAUNDRY ALLOWANCE $190.00 ABRAHIM $2,962.00 COLA $60.00 ABRAHIM $2,962.00 SHIFT ALLOW $192.00 ABRAHIM $12,000.00 COMM ALLOW $500.00 ABRAHIM $12,000.00 COVERAGE ALLOW $2,300.00 ABRAHIM $12,000.00 SERVICE PREMIUM $500.00 ABRAHIM $12,000.00 TRAVEL (NON TAXED) $1,000.00 SUTTON $12,000.00 OVERTIME $2,307.60 SUTTON $12,000.00 TRANSPORT ALLOW $1,300.00 SUTTON $12,000.00 CONT'D EDU ALLOW $1,880.00 SUTTON $12,000.00 ACTING ALLOW $2,481.60 SUTTON $12,000.00 ON CALL ALLOW $6,600.00 SUTTON $12,000.00 HEAD OF DEP'T ALLOW $1,560.00 SUTTON $12,000.00 ACTING ALLOW $1,581.60 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database function that sums?
Try something like this:
<Data<Pivot Table Use Excel Select your data Click the [Layout] button ROW: Drag the SurName field here DATA: Drag the BasicSalary field here dbl-click it and set the function to MAX Drag the Allowances field here If the function is not SUM, dbl-click it and set the function to SUM Click [OK] Select where you want the Pivot Table Then...to format it properly... Click and hold on the DATA label Drag it on top of the TOTAL label and release it. That will list each SurName, the Basic Salary and the Sum of Allowances. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Keensie" wrote: Can anyone assist me with this please, I have a spread sheet in excel in the following format, however I am now required to present each individuals basic salary and just the sum of their allowances, instead of each allowance being itemized. I am not sure how I am to accomplish this seeing that each employee has different allowances. Could anyone help? Appreciated. Surname Basic Salary Description Allowances ABRAHAM $6,190.00 ACTING ALLOW $1,581.00 ABRAHAM $6,190.00 LIVING OUT $100.00 ABRAHAM $6,190.00 NURSES INCT $1,547.50 ABRAHAM $6,190.00 MEAL ALLOWANCE $525.00 ABRAHAM $6,190.00 LAUNDRY ALLOWANCE $190.00 ABRAHAM $6,190.00 ARREARS INCENTIVE $3,814.38 ABRAHIM $2,962.00 OT - 2.0 $273.42 ABRAHIM $2,962.00 MEAL ALLOWANCE $525.00 ABRAHIM $2,962.00 LAUNDRY ALLOWANCE $190.00 ABRAHIM $2,962.00 COLA $60.00 ABRAHIM $2,962.00 SHIFT ALLOW $192.00 ABRAHIM $12,000.00 COMM ALLOW $500.00 ABRAHIM $12,000.00 COVERAGE ALLOW $2,300.00 ABRAHIM $12,000.00 SERVICE PREMIUM $500.00 ABRAHIM $12,000.00 TRAVEL (NON TAXED) $1,000.00 SUTTON $12,000.00 OVERTIME $2,307.60 SUTTON $12,000.00 TRANSPORT ALLOW $1,300.00 SUTTON $12,000.00 CONT'D EDU ALLOW $1,880.00 SUTTON $12,000.00 ACTING ALLOW $2,481.60 SUTTON $12,000.00 ON CALL ALLOW $6,600.00 SUTTON $12,000.00 HEAD OF DEP'T ALLOW $1,560.00 SUTTON $12,000.00 ACTING ALLOW $1,581.60 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database function that sums?
HI Keensie,
Can anyone assist me with this please, I have a spread sheet in excel in the following format, however I am now required to present each individual's basic salary and just the sum of their allowances, instead of each allowance being itemized. I am not sure how I am to accomplish this seeing that each employee has different allowances. Could anyone help? Appreciated. Surname Basic Salary Description Allowances ABRAHAM $6,190.00 ACTING ALLOW $1,581.00 ABRAHAM $6,190.00 LIVING OUT $100.00 ABRAHAM $6,190.00 NURSES INCT $1,547.50 Tried a pivot table? Select the range, then use the menu item Data - Pivot Table and Pivot Chart Report... click Next click Next click Layout... you will seel your column headings on the right of the dialog drag Surname to the Row area drag Salary to the Data area drag Allowances to the Data area Double-click 'Sum of Salary' in the Data area choose Average in the Summarize by list click OK click OK click Finish More info on Pivot Tables at http://www.edferrero.com/tutorials.aspx Ed Ferrero Microsoft Excel MVP http://www.edferrero.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Function that filters a list (Database) for criteria in a range a. | Excel Worksheet Functions | |||
Query a Access database that has a module from Excel | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |