Sum specific cells in column based on multiple row criteria

[generalmonk]

I cannot find an answer to this anywhere, so hoping you can help! I have a list of ref IDs in Column A which may or may not be unique (eg. 1, 1, 1, 2, 3, 3, 4, 4 ,4 ,4...etc). I need to be able to find the last instance of EACH of these ref IDs in Column A and sum the associated cell values in a column further along in the Spreadsheet. Many thanks in advance.

 

[generalmonk]

s666-PQ-sum.xlsx

Microsoft Excel Workbook

1drv.ms

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

Sorry Sandy666, thanks kindly for the table, but I am just looking for a formula... would it help if I said the last row of each ID instance was associated with the smallest number in the other column? ie. sum the smallest numbers for each ID (which translate to the last instance of each ID).

Also, Maabadi's lookup formula works but only for one ID instance (eg. ID 1), so I need to add the Lookups (Lookup with ID 1 + Lookup with ID 2, etc. until an arbitary number like 100 or so, which hopefully assumes the IDs do not go beyond 100!) However, is there a way to tell Excel to add nth lookups like this incrementing by 1 every time (representing the next ID) based on the max ID value? Hope that makes sense.

Thanks again and sorry for being a pain...

 

[generalmonk]

Thanks all, I have now resolved it with the following equation:

=SUM(XLOOKUP(UNIQUE(A5:A...),A5:A...,P5:P...,"",0,-1))