Sum Cells Based on Condition (2 problems)

[Dave Y]

Hello,

I have a spreadsheet that is used to calculate a persons YTD Gross Income.
The data contains the SS#, Name, Addr, HireDate, and YTD Gross. The sheet is
set up as such:
A B C D E
F
1. SS# Name Addr HireDate YTD Gross YTD Total
2. 111111111 Joe Schmoe 40000
3. 222222222 Jane Doe 20000 42000
4. 222222222 Jane Doe 22000
5. 555888999 Pete Moss 60000
6. 123456789 John Public 5000
25000
7. 123456789 John Public 15000 20000
8. 123456789 John Public 5000
The first problem I have is a person can work for the company numerous times
a year (for example a student who works during their breaks) and their Name,
SS#, etc... will appear on the list multiple times. Currently I am using
Conditional Formatting to highlight any duplicate rows with the exception of
the first occurrence of the SS# by using this formula in the CF dialog box
=COUNTIF($A$2:$A2,A2)>1. This works fine when it finds two or more duplicate
entries (the first entry is not highlighted). I need to be able to SUM the
YTD Gross in cell F which would be the first occurrence for a paticular
person. As in the case above for Jane Doe; she worked for the company twice
so far this year; the second entry will be highlighted yellow; her two YTD
Gross amounts will be added and placed in cell F on the first occurrence for
her data. i use this formula to accomplish this =IF(A2=A3,H2+H3,""). The CF
and this formula work fine when a person is on the list twice but if they are
the list more than twice as with John Public then the CF still works fine but
the formula will sum the 2nd and 3rd entries where I need a formula that
would sum all 3 entries and place the result in the first occurence of the
person. In the case of John Public the 3 entries would be added and the
result displayed in cell F6. How can I change my formula to make this work?

My second problem is once I have my totals in the YTD Gross Total (Column F)
I need to delete all the duplicate entries (the ones highlighted in yellow).
I am able to do this using some VBA that I found, but when the duplicates are
deleted the formulas in Column F no longer work and a #REF error is
displayed. Is there a way I can delete the highlighted rows and still keep
the dollar value in column F where it belongs?

This is a long winded post and i appreciate the time taken to read through
it. If I did not explain the issues clearly please ask and I will reply
through the post. Any help in resolving these issues will be greatly
appreciated.

Thank you,
Dave Y

 

[Barb Reinhardt]

Let's say your data is in rows 2 to 10

Try this in F2.

=IF(COUNTIF(B$2:B2,B2)=1,SUMPRODUCT(--(B$2:B$10=B2),(E$2:E$10)),NA())

You'll get NA if it's a multiple entry.

 

[daddylonglegs]

Hi Barb,

Can I suggest an improvement? SUMIF will suffice here as there is only a
single criterion, i.e.

=IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B$2:B$10,B2,E$2:E$10),NA())

 

[Dave Y]

Hi barb and Daddylonglegs,

Thank you so much for your responses. It worked out perfectly. I really
appreciate your assistance.

Have a great weekend,

Dave Y