For this kind of problem, it helps to think in terms of arrays.

If you can get an array (list) of the numbers in Values (column D) where Letters (column B) equals "A" and Numbers has duplicates removed, you could just sum the array to get the answer.

This expression:

`(B$2:B$12=F2)`

gives an array of `True/False`

values with `True`

wherever column B = "A". This one:

`(C$2:C$12<>C$3:C$13)`

gives an array of `True/False`

values with `True`

wherever a cell in column C is not equal to the following cell. Because your duplicates are in sequential cells (comment below if this will not always be the case), this array has `False`

where the extra occurrences of a value are, and, essentially, it filters out the duplicates. Multiplying these two arrays together:

`(B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)`

converts `True/False`

values to 1's and 0's, and gives an array with 1 in the positions that we want to be in the sum. Using this array as the `logical_test`

in an `IF()`

, and column D as the `value_if_true`

:

`IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12)`

returns an array of the Values in column D wherever there was a 1, interspersed with `False`

wherever there was a zero. Now we can just sum the array. This formula, filled down from G2 gives the results shown below.

`=SUM(IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12))`

Note that this is an array formula and must be entered with `CTRL``Shift``Enter`.