Excel 2007 : Need to get subtotal based on another column's unique values

I have an excel workbook with 4,398 rows. This workbook has multiple filters. What I want to do is have a total of values based on the unique values in another column...BUT, tricky part...want the MAX counted only. Let me demonstrate:

Mileage pkey
0.7 7204
0.01 11450
1.36 6735
5.9 10034
1.36 6735
0.15 6636
0.93 7763

In this example the total for MILEAGE is what I am looking for based on Unique pkey. This is also a FILTERED subset of the data so it would have to know to only total these 7 not the entire 4398 rows. My total in this example SHOULD be 9.05.

Now lets throw a second example up - lets say the data looks like this

Mileage pkey
0.7 7204
0.01 11450
1.36 6735
5.9 10034
1.5 6735
0.15 6636
0.93 7763

Notice for pkey 6735 I have two mileages - I just want the MAX mileage added. So my total in this case would be 9.19 (again a filtered subset of almost 5000 records with 5 other columns being filtered, such as date and state, etc). I dont mind a VBA function, or a button to push to get the answer - - I would prefer to just have it update automatically on filter though.

I am very comfortable with VBA and Macros and functions / formulas / arrays. Getting close with a lot of help from this forum - just not quite gettting the right result set.

Any help??? Thanks!!!!

Try this UDF, I gave up with native formula (That should bring out the cavalry.....)

Please Login or Register  to view this content.

Enter in Excel

Please Login or Register  to view this content.

Syntax
SumMaxVisiblePkey(Pkey Range,Mileage Range)

If you use whole column references the function will trim to Row1 to Last Used Row

If you have Numeric data above or below the query range then enter the function
Like

Please Login or Register  to view this content.

When you apply filters the function will recalculate the visible rows.

Hope this helps