Sum a quantity once for multiple entries

[Guest]

I am using Office 2003 on Windows XP.

Suppose I have the following (hopefully it will line up):

ID Qty
232 50
232 50
232 50
628 10
628 10
550 63
728 82
009 14
009 14

Is there a formula I can use in the spreadsheet that will sum the quantities
(Qty) column, but only once for each ID? If so, could someone please post a
solution?

I could code something in VBA, but I'd prefer to write a formula. Can it be
done?

Thanks much in advance.

 

[Guest]

=Sumproduct(--(A1:A50=232),--(B1:B20))

HTH

 

[Guest]

Thanks Toppers, but I need the sum of all the items in the list once, there
could be several hundred...in my example there would be one total: 219

Any ideas for this scenario?

 

[Bob Phillips]

=sumif(a:a,232,b:b)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks Bob, but I'm afraid I didn't really explain what I was really after
very well. Using my original example, what I'm after is: 50+10+63+82+14= 219

i.e. Include ALL items, but each only once in the total. Any further ideas?

 

[Don Guillett]

In a helper column (F), put this formula in row 2 and copy down
=IF(COUNTIF($A$2:A2,A2)=1,1,0)
then just use
=SUMIF(F:F,1,B:B)

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A10<>A1:A9),B2:B10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Note the ranges that I used in case you extend them in the real situation

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Don Guillett]

I had forgotten that one. Getting old.

 

[Bob Phillips]

Obviously still celebrating the weekend

Bob

I had forgotten that one. Getting old.