I have two columns

one has "id" and column 2 has amount

a 5
b 10
c 15
a 5
c 15

All id has same amount.
what I want is total of all unique id's
i.e. 30 in this case

The number of rows/ records can go up and down.
any takers?

Try this:

With
your posted example in A1:B5

C1: =SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5)*B1:B5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"flow23" wrote:

I have two columns

one has "id" and column 2 has amount

a 5
b 10
c 15
a 5
c 15

All id has same amount.
what I want is total of all unique id's
i.e. 30 in this case

The number of rows/ records can go up and down.
any takers?

Yes it works except that the range changes.i.e., the number of row varies.


"Ron Coderre" wrote:

Try this:

With
your posted example in A1:B5

C1: =SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5)*B1:B5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"flow23" wrote:

I have two columns

one has "id" and column 2 has amount

a 5
b 10
c 15
a 5
c 15

All id has same amount.
what I want is total of all unique id's
i.e. 30 in this case

The number of rows/ records can go up and down.
any takers?

If the number of rows in the range varies, then maybe Dynamic Named Ranges
are what you need. See Debra Dalgleish's website for instructions:

http://www.contextures.com/xlNames01.html#Dynamic
Post back with any questions.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"flow23" wrote:

Yes it works except that the range changes.i.e., the number of row varies.


"Ron Coderre" wrote:

Try this:

With
your posted example in A1:B5

C1: =SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5)*B1:B5)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"flow23" wrote:

I have two columns

one has "id" and column 2 has amount

a 5
b 10
c 15
a 5
c 15

All id has same amount.
what I want is total of all unique id's
i.e. 30 in this case

The number of rows/ records can go up and down.
any takers?