| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to remove duplicates and combine their unique values? | Excel Discussion (Misc queries) | |||
| Sum duplicates unique values and delete dupliques | Excel Worksheet Functions | |||
| check for duplicates, then sum unique values | Excel Discussion (Misc queries) | |||
| Count Unique Values In A Filtered Row with Duplicates | Excel Discussion (Misc queries) | |||
| Count unique values among duplicates in a subtotal range | Excel Discussion (Misc queries) | |||
Hybrid Mode
