I have a spreadsheet on employee deductions. Since it's the beginning of the
year I need to verify if every employee has a specific deduction and if it
has been calculated correctly. My report is laid out as follows:

EE Name Salary Deduction Code Deductiont Amount
Sam $2000 A $120
Sam $2000 B $80
Sam $2000 C $10
Sam $2000 D $17
Sam $2000 W $43
Dave $2500 A $150
Dave $2000 B $80
Dave $2000 C $10
Dave $2000 D $17
Dave $2000 W $43

Deduction code "A" is what I'm looking for in each record. So for each
unique Employee Name I need to verify that there is a Deduction code A listed
and that the calculation of deduction code "A" is equal to the formula of
Salary * 6%.

I tried to accomplish this by doing an if statement to find "A" in the
deduction code column C and putting YES beside it then doing the calculation
for the amount for everyone that had YES, but I'm worried that approach
wouldn't reveal if an employee was missing deduction Code "A"...so I think I
need something that would say for each unique Name find a deduction code A.

Thanks in advance!!

Dedrie,

I put data, like yours, in cells A2:D10. IN my case I had 3 distinct names.
In cells A13:A15 I listed the names.

In cell B13 I have:
=SUM(IF($A$2:$A$10=A13,IF($C$2:$C$10="A",$B$2:$B$1 0,0),0))

In cell C13 I have:
=SUM(IF($A$2:$A$10=A13,IF($C$2:$C$10="A",$D$2:$D$1 0,0),0))

I thiink that this gets you what you'll need. Now a few comments.

First, you can get a unique list of names using the Advanced Filter (under
data).
Check "unique values" and "copy to a another location".

The 2 formulas I listed are "array formulas". In order to enter them, you
have to type them and then hit ctrl-shift-enter. If you do that, you'll see
curly brackets around the formula. You cannot type those brackets to make it
an array formula, they just let you know that you've entered an array formula.


Good luck.

Art


"Dedrie" wrote:

I have a spreadsheet on employee deductions. Since it's the beginning of the
year I need to verify if every employee has a specific deduction and if it
has been calculated correctly. My report is laid out as follows:

EE Name Salary Deduction Code Deductiont Amount
Sam $2000 A $120
Sam $2000 B $80
Sam $2000 C $10
Sam $2000 D $17
Sam $2000 W $43
Dave $2500 A $150
Dave $2000 B $80
Dave $2000 C $10
Dave $2000 D $17
Dave $2000 W $43

Deduction code "A" is what I'm looking for in each record. So for each
unique Employee Name I need to verify that there is a Deduction code A listed
and that the calculation of deduction code "A" is equal to the formula of
Salary * 6%.

I tried to accomplish this by doing an if statement to find "A" in the
deduction code column C and putting YES beside it then doing the calculation
for the amount for everyone that had YES, but I'm worried that approach
wouldn't reveal if an employee was missing deduction Code "A"...so I think I
need something that would say for each unique Name find a deduction code A.

Thanks in advance!!

Dedrie,

Let's say that your data table starts in cell A1 and extends down to row 1000.

In cell E2, enter the following formula

=IF(SUMPRODUCT(($A$1:$A$1000=A2)*($C$1:$C$1000="A" ))<1,SUMPRODUCT(($A$1:$A$1000=A2)*($C$1:$C$1000=" A"))&"
A's for "&A2,IF(C2="A",IF(D2=0.06*B2,"OK","Calc Off"),""))

and copy down to Cell E1000. This will highlight the missing and extra A's and the equations that
aren't 6%...

HTH,
Bernie
MS Excel MVP


"Dedrie" wrote in message
...
I have a spreadsheet on employee deductions. Since it's the beginning of the
year I need to verify if every employee has a specific deduction and if it
has been calculated correctly. My report is laid out as follows:

EE Name Salary Deduction Code Deductiont Amount
Sam $2000 A $120
Sam $2000 B $80
Sam $2000 C $10
Sam $2000 D $17
Sam $2000 W $43
Dave $2500 A $150
Dave $2000 B $80
Dave $2000 C $10
Dave $2000 D $17
Dave $2000 W $43

Deduction code "A" is what I'm looking for in each record. So for each
unique Employee Name I need to verify that there is a Deduction code A listed
and that the calculation of deduction code "A" is equal to the formula of
Salary * 6%.

I tried to accomplish this by doing an if statement to find "A" in the
deduction code column C and putting YES beside it then doing the calculation
for the amount for everyone that had YES, but I'm worried that approach
wouldn't reveal if an employee was missing deduction Code "A"...so I think I
need something that would say for each unique Name find a deduction code A.

Thanks in advance!!