How to sum (data in col3) based on criteria in col1 excluding duplicates in col2?

Question

I need an advice: I have managed to do most of stuff on my file although there is one thing I can't get around:

How to sum data in column C, based on the column A value, excluding duplicate values from B. I'm not sure if I'm clear enough but e.g. formula has to:

look at values in col A, and if it identifies e.g. a123, then look at col B and if there are duplicates such as 1aa (happens twice for a123), then sum excluding one duplicate i.e. sum only -11,-23 and -50.

Same goes for any other value in col A e.g. b456 (only sum -11, -23 and -30)

I've tried sumproduct but it sums without consideration for duplicates.

Something like this:

Thanks

Answer 1

use this array formula:

=SUM(IF($A$1:$A$11=E2,$C$1:$C$11/COUNTIFS(A:A,E2,B:B,$B$1:$B$11)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

This assumes the values to be the same for each combinations.