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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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