Problem (from the German MS-Excel-Newsgroup of 2007/04/17):

A B C D 1 Customer Value 2 C 1 3 A 2 4 B 3 5 B 4 6 A 5 7 C 6 8 A 7 9 C 8 10 B 9 11 A 10 12 The last: 3 13 Sum: 22 for Customer: A

Solution (Klaus "Perry" Pago of 2007/04/17):

B13: =SUMPRODUCT(N(OFFSET(B2,LARGE((A2:A11=$D$13)*ROW(INDIRECT("1:"&ROWS(A2:A11))),ROW(INDIRECT("1:"&$D$12)))-1,)))

Note: This works also with SUM, AVERAGE and other statistical functions of the same ranking, like
MAX, when entered as an array formula (Ctrl-Shift-Enter).

Comment: Klaus found a formula which orients on the cells, not on its contents, which often is complicated and thus very useful!

Using SMALL, you reach the first 3 entries instead of the last. By varying ROW(INDIRECT("1:"&D12)) you reach any other desired position.