Database function that sums?

[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
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

 

[Bob Phillips]

Salary:

=INDEX(B:B,MATCH("ABRAHAM",B:B,0))

Allowances

=SUMIF(A:A,"ABRAHAM",D)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ron Coderre]

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

 

[Ed Ferrero]

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