CODEWITHSUNDEEP
excelexcel-formula
Ahndwoo
Ahndwoo

Reputation: 1025

Sum rows containing only the first appearance of value in separate column

I have a table that looks something like this:

Column A | Column B
    A        1
    B        2
    C        3
    A        4

What I want to do is get the sum of the values in Column B, but only first the first occurrence of each value in Column A. Thus, the result I want to get is (1 + 2 + 3) = 6 (Adding the first three rows, but omitting the fourth, because a row with 'A' in Column A has already included in the sum).

I've tried looking at Frequency, but I haven't been able to figure out how to use it properly to get the result I want.

Upvotes: 1

Views: 823

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use SUMPRODUCT and MATCH:

=SUMPRODUCT(--(ROW(A1:A4)=MATCH(A1:A4,A:A,0)),B1:B4)

enter image description here

Upvotes: 5

Related Questions