SUM but only the last instance / occurrence if there are duplicates in Excel

This is a sample of office Stats and I want to SUM all of the "Dizziness" from C2:H6 but only take the LAST instance if there is a duplicate in a Patient Name. I've seen somewhere posted SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10&"")) for finding the first instance, but not the last. Note: Because this is a patient database, there will be multiple duplicates.

Screenshot of table

Timestamp   Name    Condition 1 Rate    Condition 2 Rate    Condition 3 Rate
3/6/18      Test 2  Dizziness   5   Fatigue     5   Fainting    5
3/7/18      Test 3  Sciatica    3   Dizziness   5   Tinnitus    10
3/8/18      Test 2  Dizziness   5   Fainting    6   Fatigue     6
3/9/18      Test 4  Neck Pain   6   Sciatica    6   Vertigo     6
3/10/18     Test 2  Fatigue     8   Fainting    8   Dizziness   10

        DIZZINESS   15      25

I know my result should be 15 from Test 2 and Test 3, but I don't want to capture Test 2's duplicate from other dates which would give me a total of 25 (was using =SUMIFS(D2:H6,C2:G6,C8). Help please?

James

Solution

Office 365 Excel:

=SUMPRODUCT((A2:A6=MAXIFS(A:A,B:B,B2:B6))*($C$2:$G$6=C8),D2:H6)

Earlier versions:

Use a helper column to store the max date, put this in I2 and copy down:

=AGGREGATE(14,6,$A$2:$A$6/($B$2:$B$6=B2),1)

Then you can use this formula:

=SUMPRODUCT((D2:H6),($C$2:$G$6=C8)*(A2:A6=I2:I6))