Remember Me?

Menu

#1
August 10th 07, 11:20 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2007 Posts: 1
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
August 10th 07, 12:40 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 698
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
August 10th 07, 12:54 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 698
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.

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Dave Excel Discussion (Misc queries) 3 June 11th 07 07:51 AM Dave Excel Worksheet Functions 1 June 7th 07 01:02 PM Weissme Excel Discussion (Misc queries) 0 August 9th 06 04:35 PM jcpotwor Excel Discussion (Misc queries) 1 January 13th 06 02:02 AM jcpotwor Excel Discussion (Misc queries) 2 January 12th 06 02:29 PM

All times are GMT +1. The time now is 07:07 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.

About Us

"It's about Microsoft Excel"

Copyright © 2017