function for sorting/counting

[awelliott]

Hi all,

I have a two column worksheet, ColumnA is a list of names and ColumnB is a list of numbers.

Example: (formatting in HTML is a little screwy but...)

A B
Derrek 10
Frank 8
Jane 20
Derrek 50
Sue 30
Sue 30
Tom 20
Emily 15
Derrek 14

The list is extremely long, and has multiples of the same name. I would like to get a total for ColumnB, but only count the highest number when there are duplicates.

So in this example, the total should end up being: 143.

The problem I am encountering is that when I remove duplicates, it is only removing Sue's 30 since both ColumnA and ColumnB are the same. It does not remove Derrek's 14 or Derrek's 10 since ColumnB is not a duplicate number.

Is there a function or even a multi-step process I can use to solve this issue?

Thanks in advance!

Drew

[NBVC]

Perhaps in 2 steps:

If data is A1:B9, then:

=IF(SUMPRODUCT(--(A$1:A1=A1),--(B$1:B1=B1))>1,"",IF(B1=MAX(IF($A$1:$A$9=A1,$B$1:$B$9)),B1,""))

adjust ranges to suit and then confirmed with CTRL+SHIFT+ENTER and copied down...

Then sum that column.

[sailepaty]

One more option, data in range A1:B9.

Formula in C1 copy down to C9
=IF(COUNTIF($A$1:A1,A1)=1,MAX(INDEX(($A$1:$A$9=A1)*$B$1:$B$9,0)),0)

Total
=SUM(C1:C9)

Regards

[awelliott]

Great!

I'm going to try these two out this weekend and get back to you.

Thank you both for your help!

Drew

[awelliott]

Hi Guys,

I was able to get the second formula to work well. The only thing to note is that sometimes the first cell in ColumnB is counted when it is not the largest number. So its important to check that when reviewing your results. Everything else worked perfectly.

Thanks again!!

Drew

[NBVC]

Are you sure that all the names that matched that first cell had no extra spaces or something that makes them not exact matches?

[awelliott]

You're right! There was a space. Thanks again.

Drew