Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.