Excel - Sum First Value Of Non-Unique Adjacent Column

Question

I have a table:

ColA  |  ColB
  1       1
  2       1
  2       1
  3       1
  4       1
  5       1

I would like to sum ColB only once where ColA = 2.

Essentially, I would like to sum the values of ColB as if the table looked like

ColA  |  ColB
  1       1
  2       1
  3       1
  4       1
  5       1

So the sum of ColB should be 5 not 6

Any ideas on how I can do this?

This site got me close, but it doesn't add either of the ColB values for the rows where ColA = 2.

Answer 1

Use unique filter with a simple =SUM formula

1. Select your table (A1:B7)

2. Go to: Menu Bar » Data » Filter » Special Filter

3. Check No duplicates and Copy to another location

4. Choose a single cell somewhere right to your table as Copy to location and press OK

5. Insert a =SUM(E2:E6) formula below your new ColB values

   

Note: This will only work if ColB values and corresponding ColA values are the same - like you said in your comment.

Answer 2

Introduce column C, assuming first row is labels, this formula starts in row 2

=COUNTIF($A$1:A2,A2)

Copy down and do a Sumif on column B where column c = 1

=SUMIF(C:C,1,B:B) or

=SUMIFS(B:B,C:C,1)

Answer 3

This single formula should give the required result

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