sum only one instance of duplicate

[Isacael]

Hi All

I am after a formula (preferably subtotal), whereby I can sum only one instance of a duplicate. From the below data for eg, I want to return a sum value of 110 for R1087 and not 220.

Cheers



R1087 110
R1087 110
R1086 710
R1086 710
R1086 710
R1086 710
R1045 600
R1045 600

[DonkeyOte]

Is the relationship between code and value 1:1 as implied in the sample dataset ?
(ie all instances of R1087 will be 110)

If so, calculate the Average (ie SUMIF / COUNTIF) or just conduct a VLOOKUP to return first value for given code.

[Isacael]

Thanks DonkeyOte, but Im afraid I have not explained it well. Please see example attached.

There are a couple of conditions that I need to apply to get the right subtotal. Hope it makes sense!

Cheers

[DonkeyOte]

I confess I'm struggling to come up with something elegant for a single cell calculation (ie no helper cells)

Using your sample the below should work:

=SUMPRODUCT(--(A6:A12=A6:A12),--(MATCH(A6:A12&"@"&D6:D12,A6:A12&"@"&D6:D12,0)=(ROW(D6:D12)-ROW(D6)+1)),--(D6:D12=SUBTOTAL(4,OFFSET(D6:D12,MATCH(A6:A12,A6:A12,0)-1,0,COUNTIF(A6:A12,A6:A12),1))),D6:D12)

however it's ugly, inefficient and volatile and there must be a better way.

If you used helper calcs you could greatly simplify proceedings.

[martindwilson]

id probably go for a helper
=IF(COUNTIF($A$6:A6,A6)>1,"",MAX(($A$6:$A$12=A6)*($D$6:$D$12)))
array enterred then dragged down and summed