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