Sum values based on first occurrence of other column using excel formula

Question

Let's say I have the following two columns in excel spreadsheet

A   B
1   10
1   10
1   10
2   20
3   5
3   5

and I would like to sum the values from B-column that represents the first occurrence of the value in A-column using a formula. So I expect to get the following result:

result = B1+B4+B5 = 35

i.e., sum column B where any unique value exists in the same row but Column A. In my case if Ai = Aj, then Bi=Bj, where i,j represents the row positions. It means that if two rows from A-column have the same value, then its corresponding values from B-column are the same. I can have the value sorted by column A values, but I prefer to have a formula that works regardless of sorting.

I found this post that refers to the same problem, but the proposed solution I am not able to understand.

Answer 1

Use SUMPRODUCT and COUNTIF:

=SUMPRODUCT(B1:B6/COUNTIF(A1:A6,A1:A6))

Here the step by step explanation:

1. COUNTIF(A1:A6, A1:A6) will produce an array with the frequency of the values: A1:A6. In our case it will be: {3, 3, 3, 1, 2, 2}
2. Then we have to do the following division: {10, 10, 10, 20, 5, 5}/{3, 3, 3, 1, 2, 2}. The result will be: {3.33, 3.33, 3.33, 20, 2.5, 2.5}. It replaces each value by the average of its group.
3. Summing the result we will get: (3.33+3.33+3.33) + 20 + (2.5+2.5=35)=35.

Using the above trick we can just get the same result as if we just sum the first element of each group from the column A.

---

To make this dynamic, so it grows and shrinks with the data set use this:

=SUMPRODUCT($B$1:INDEX(B:B,MATCH(1E+99,B:B))/COUNTIF($A$1:INDEX(A:A,MATCH(1E+99,B:B)),$A$1:INDEX(A:A,MATCH(1E+99,B:B))))

Answer 2

... or just SUMPRODUCT.

=SUMPRODUCT(B2:B7, --(A2:A7<>A1:A6))