# Excel - Sum First Value Of Non-Unique Adjacent Column

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.

-
If we assume that `A2` = 1, `A3` = 2, and `A4` also = 2, what do you want to have happen if `B3``B4`? (Or are all the `B` values equal to 1? If so, you’re talking about a count rather than a sum.) Do you guarantee that the `A` values are sorted? – Scott Feb 5 '13 at 1:45
if column A has a duplicate, then the adjacent value in column B will be the same as the corresponding value to the duplicate column – Jeffrey L. Roberts Feb 5 '13 at 3:14

Use unique filter with a simple =SUM formula

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.

-

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)

-

This single formula should give the required result

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

-