Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
2  
If we assume that A2 = 1, A3 = 2, and A4 also = 2, what do you want to have happen if B3B4? (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
up vote 1 down vote accepted

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

    enter image description here

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

share|improve this answer

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)

share|improve this answer

This single formula should give the required result

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .