Sum a quantity once for multiple entries
 

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.

Sum a quantity once for multiple entries
 

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

HTH

"XP" wrote:

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.

Sum a quantity once for multiple entries
 

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?

"Toppers" wrote:

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

HTH

"XP" wrote:

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.

Sum a quantity once for multiple entries
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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.

Sum a quantity once for multiple entries
 

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?

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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.

Sum a quantity once for multiple entries
 

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)
--
Don Guillett
SalesAid Software

"XP" wrote in message
...
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?

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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.

Sum a quantity once for multiple entries
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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?

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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.

Sum a quantity once for multiple entries
 

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)

"XP" wrote in message
...
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?

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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.

Sum a quantity once for multiple entries
 

I had forgotten that one. Getting old.

--
Don Guillett
SalesAid Software

"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A2:A10<A1:A9),B2:B10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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?

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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.

Sum a quantity once for multiple entries
 

Obviously still celebrating the weekend :-)

Bob

"Don Guillett" wrote in message
...
I had forgotten that one. Getting old.

--
Don Guillett
SalesAid Software

"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A2:A10<A1:A9),B2:B10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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?

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"XP" wrote in message
...
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.