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

0
votes

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.

2
excelexcel-formula
You could probably just sum unique. I'm sure google can find an array formula for that. – user4039065
I made several searches but I did not find a solution for my specific problem. This is the nearest one I have found but is not my exact problem. – David Leal

2 Answers

3
votes

Use SUMPRODUCT and COUNTIF:

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

enter image description here

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))))
2
votes

... or just SUMPRODUCT.

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

enter image description here