Sum of Unique Values

[ollieotis]

Hello,

I have a worksheet that has multiple projects assigned to multiple individuals, with overlap between those individuals, and associated sales dollars per project. All of those projects then roll-up to one individual. I'd like to create a sum formula of sorts to total the projects without duplicating those that are listed twice. Here's roughly how it's set-up:

ABC01234 $1,000
DEF01234 $3,000
FDG01234 $4,000
Total John Smith $8,000

ABC01234 $1,000
XYZ56789 $5,000
ANO01234 $3,000
Total Jane Smith $9,000

Total Top Dog $16,000 (Excl ABC01234 duplicate)

I'm using a reporting system that is based off of excel 2003, and trying to create a report I can run on a batch type basis which won't require me to do anything offline. I believe this limits me to using some sort of a function for my total vs advanced filters or pivot tables to get an answer I need. Of course, I've always found new methods just by asking the question on this forum...

Thanks in advance for any assistance!

 

[Aladin Akyurek]

Hello,

I have a worksheet that has multiple projects assigned to multiple individuals, with overlap between those individuals, and associated sales dollars per project. All of those projects then roll-up to one individual. I'd like to create a sum formula of sorts to total the projects without duplicating those that are listed twice. Here's roughly how it's set-up:

ABC01234 $1,000
DEF01234 $3,000
FDG01234 $4,000
Total John Smith $8,000

ABC01234 $1,000
XYZ56789 $5,000
ANO01234 $3,000
Total Jane Smith $9,000

Total Top Dog $16,000 (Excl ABC01234 duplicate)

I'm using a reporting system that is based off of excel 2003, and trying to create a report I can run on a batch type basis which won't require me to do anything offline. I believe this limits me to using some sort of a function for my total vs advanced filters or pivot tables to get an answer I need. Of course, I've always found new methods just by asking the question on this forum...

Thanks in advance for any assistance!

Control+shift+enter, not just enter...

=SUM(
   IF(1-ISNUMBER(SEARCH("Total",A2:A10)),
   IF(B2:B10,
     B2:B10/COUNTIF(A2:A10,A2:A10))))