Summing largest value per individual

My guess is there is a non volatile solution, but my effort...

=MAX(OFFSET($A$1,MATCH($F$1,$A$2:$A$27,0),1,COUNTIF($A$2:$A$27,$F$1)))

Where F1 holds the Account number

use a pivot table see attached

I like Martin's suggestion with the pivot table as it gives you everything in one feel swoop.

Here is the workbook with the formula.

right click table in options uncheck grand total then put =sum(f:f) or sum (f3:f10000) somewhere this will total the maxes
you can make the range bigger and hide blanks
when new data is added right click table and refresh
this one has the range set to a1:b400 and the grand total hidden
add something to the bottom then refresh

This can be done with a few setup steps.

First, create a named range
MyPivot =OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),2)

Click on the pivot table >> on the ribbon >> Options >> Data >> Change Data Source >> Table/Range: MyPivot

Right click on the max column in the pivot table >> value field settings >> Sum

Now every time data is added, right click on the pivot table and select refresh.

This could also be done (the refresh) with a small macro

not within the pivot table-if you change to sum all the other rows change to sum too. you need a sum formula above the pivot and turn off the grand total row. or use a helper column and formulas assuming your data is sorted by account

yea i updated my post to hide grand total then use sum(f3:f10000) instead