software

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.60Salary:

=INDEX(B:B,MATCH(quot;ABRAHAMquot;,B:B,0))

Allowances

=SUMIF(A:A,quot;ABRAHAMquot;,D)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Keensiequot; gt; wrote in message
...
gt; Can anyone assist me with this please, I have a spread sheet in excel in
the
gt; following format, however I am now required to present each individual's
gt; basic salary and just the sum of their allowances, instead of each
allowance
gt; being itemized. I am not sure how I am to accomplish this seeing that each
gt; employee has different allowances. Could anyone help? Appreciated.
gt;
gt; Surname Basic Salary Description
Allowances
gt; ABRAHAM $6,190.00 ACTING ALLOW $1,581.00
gt; ABRAHAM $6,190.00 LIVING OUT $100.00
gt; ABRAHAM $6,190.00 NURSES INCT $1,547.50
gt; ABRAHAM $6,190.00 MEAL ALLOWANCE $525.00
gt; ABRAHAM $6,190.00 LAUNDRY ALLOWANCE $190.00
gt; ABRAHAM $6,190.00 ARREARS INCENTIVE $3,814.38
gt; ABRAHIM $2,962.00 OT - 2.0
$273.42
gt; ABRAHIM $2,962.00 MEAL ALLOWANCE $525.00
gt; ABRAHIM $2,962.00 LAUNDRY ALLOWANCE $190.00
gt; ABRAHIM $2,962.00 COLA $60.00
gt; ABRAHIM $2,962.00 SHIFT ALLOW $192.00
gt; ABRAHIM $12,000.00 COMM ALLOW $500.00
gt; ABRAHIM $12,000.00 COVERAGE ALLOW $2,300.00
gt; ABRAHIM $12,000.00 SERVICE PREMIUM $500.00
gt; ABRAHIM $12,000.00 TRAVEL (NON TAXED) $1,000.00
gt; SUTTON $12,000.00 OVERTIME $2,307.60
gt; SUTTON $12,000.00 TRANSPORT ALLOW $1,300.00
gt; SUTTON $12,000.00 CONT'D EDU ALLOW $1,880.00
gt; SUTTON $12,000.00 ACTING ALLOW $2,481.60
gt; SUTTON $12,000.00 ON CALL ALLOW $6,600.00
gt; SUTTON $12,000.00 HEAD OF DEP'T ALLOW $1,560.00
gt; SUTTON $12,000.00 ACTING ALLOW $1,581.60
gt;
Try something like this:

lt;Datagt;lt;Pivot Tablegt;
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-Proquot;Keensiequot; wrote:

gt; Can anyone assist me with this please, I have a spread sheet in excel in the
gt; following format, however I am now required to present each individual’s
gt; basic salary and just the sum of their allowances, instead of each allowance
gt; being itemized. I am not sure how I am to accomplish this seeing that each
gt; employee has different allowances. Could anyone help? Appreciated.
gt;
gt; Surname Basic Salary Description Allowances
gt; ABRAHAM $6,190.00 ACTING ALLOW $1,581.00
gt; ABRAHAM $6,190.00 LIVING OUT $100.00
gt; ABRAHAM $6,190.00 NURSES INCT $1,547.50
gt; ABRAHAM $6,190.00 MEAL ALLOWANCE $525.00
gt; ABRAHAM $6,190.00 LAUNDRY ALLOWANCE $190.00
gt; ABRAHAM $6,190.00 ARREARS INCENTIVE $3,814.38
gt; ABRAHIM $2,962.00 OT - 2.0 $273.42
gt; ABRAHIM $2,962.00 MEAL ALLOWANCE $525.00
gt; ABRAHIM $2,962.00 LAUNDRY ALLOWANCE $190.00
gt; ABRAHIM $2,962.00 COLA $60.00
gt; ABRAHIM $2,962.00 SHIFT ALLOW $192.00
gt; ABRAHIM $12,000.00 COMM ALLOW $500.00
gt; ABRAHIM $12,000.00 COVERAGE ALLOW $2,300.00
gt; ABRAHIM $12,000.00 SERVICE PREMIUM $500.00
gt; ABRAHIM $12,000.00 TRAVEL (NON TAXED) $1,000.00
gt; SUTTON $12,000.00 OVERTIME $2,307.60
gt; SUTTON $12,000.00 TRANSPORT ALLOW $1,300.00
gt; SUTTON $12,000.00 CONT'D EDU ALLOW $1,880.00
gt; SUTTON $12,000.00 ACTING ALLOW $2,481.60
gt; SUTTON $12,000.00 ON CALL ALLOW $6,600.00
gt; SUTTON $12,000.00 HEAD OF DEP'T ALLOW $1,560.00
gt; SUTTON $12,000.00 ACTING ALLOW $1,581.60
gt;

HI Keensie,

gt; Can anyone assist me with this please, I have a spread sheet in excel in
gt; the
gt; following format, however I am now required to present each individual's
gt; basic salary and just the sum of their allowances, instead of each
gt; allowance
gt; being itemized. I am not sure how I am to accomplish this seeing that each
gt; employee has different allowances. Could anyone help? Appreciated.
gt;
gt; Surname Basic Salary Description
gt; Allowances
gt; ABRAHAM $6,190.00 ACTING ALLOW $1,581.00
gt; ABRAHAM $6,190.00 LIVING OUT $100.00
gt; 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 www.edferrero.com/tutorials.aspx

Ed Ferrero
Microsoft Excel MVP
www.edferrero.com