How do I sum unique values among duplicates horizontally
 

I am trying to sum unique values among duplicates horizontally. I cannot use
the frequency formula as this only works if the values are listed vertically.
The version of excel I am using is Microsoft Excel 2003.

How do I sum unique values among duplicates horizontally
 

For numbers or blanks (but no text) in B1:K1

Try this:
A1: =SUMPRODUCT((MATCH(B1:K1+0,B1:K1+0,0)=COLUMN(B1:K1 )-1)*B1:K1)

or this
A1: =SUMPRODUCT((B1:K1<"")/COUNTIF(B1:K1,B1:K1&"")*B1:K1)

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

XL2003, WinXP


"Janine Ball" wrote:

I am trying to sum unique values among duplicates horizontally. I cannot use
the frequency formula as this only works if the values are listed vertically.
The version of excel I am using is Microsoft Excel 2003.

How do I sum unique values among duplicates horizontally
 

One other comment....
Technically, FREQUENCY can be made to work against horizontal cell ranges:

=SUMPRODUCT((TRANSPOSE(FREQUENCY(B1:K1,B1:K1+0))0 )*B1:L1)

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

XL2003, WinXP


"Ron Coderre" wrote:

For numbers or blanks (but no text) in B1:K1

Try this:
A1: =SUMPRODUCT((MATCH(B1:K1+0,B1:K1+0,0)=COLUMN(B1:K1 )-1)*B1:K1)

or this
A1: =SUMPRODUCT((B1:K1<"")/COUNTIF(B1:K1,B1:K1&"")*B1:K1)

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

XL2003, WinXP


"Janine Ball" wrote:

I am trying to sum unique values among duplicates horizontally. I cannot use
the frequency formula as this only works if the values are listed vertically.
The version of excel I am using is Microsoft Excel 2003.