Need to identify highest frequency value with multiple duplicates and break ties

[mclawren]

I've got a spread sheet where I track defective parts in one column and the corresponding costs associated with each instance in another column.  Because these parts are inspected in small batches there are frequent duplicate values of multiple parts, and often there are ties, ie part xxx, yyy and zzz may each show up 3 different times on this list.  I want a formula to identify the part with the highest frequency, and when there are ties I want to break that tie by highest total cost for each part.  Right now I'm using INDEX, MODE.MULT and MATCH to identfy all duplicates and the total cost of each duplicate part, but considering this is a rolling 7 day record I really just want the most costly part to help quickly identify what job processes need to be looked at for issues.  Is there a single formula I can use to do this?

[#2]

@mclawren 

You don't mention the MAX function as one you've tried to incorporate. From what you've described, not seeing your actual sheet, it's something I'd suggest....

[#3]

Thanks, I used that in conjunction with all the date my INDEX formulas gave me and got the info I wanted, but it's still rather messy. This is something I see using repeatedly so I'm hoping to find a single (complex) formula that might do this, if that's possible that is.

[#4]

@mclawren 

I'm hoping to find a single (complex) formula that might do this, if that's possible that is.

Single complex formulas are possible, of course. They're also nightmares. Most texts on Excel warn against them unless they're unavoidable. They're awfully fun to write, cause for celebration when they first work.

But then three months later when you need to, uh, fix something again, they're oh-so-hard to deconstruct and remember what (this (nested function (did inside of (yet another nested conditional, (etc., (etc., ))))))

Far preferable (IMHO) to write formulas in adjacent columns (helper columns) that finally deliver the desired result,  each of which can be comprehended at a glance.

[#5]

This is the first time in my career I've gotten this in depth with excel, so still learning a lot. Thanks for the advice and that does make sense.