Excel Formula Help

Question

I currently have the following formula is place:

=SUMIF(AUM!$C:$C,$A484,AUM!K:K)

On my Summary Sheet, column A is the name of a person. ($A484)  

On the AUM sheet, column C is the name of that same person. (AUM!$C:$C)

On the AUM sheet, column K is the value of multiple accounts. (AUM!K:K)

Some people have multiple accounts and the formula does a perfect job grabbing the total value for the person. 

The issue is when a person has duplicate accounts, the formula counts the value twice. 

I need to ignore duplicate account values.  

Example Below: 

Summary Sheet

Name	Total Value
John Doe	550
Mike Smith	600	WRONG
	350	CORRECT

AUM Sheet

Name	Acc #	Value
John Doe	1234	250
John Doe	4321	300
Mike Smith	7777	100
Mike Smith	8888	250
Mike Smith	8888	250

Answer 1

Hi,

Here's the PowerPivot solution.  Try this measure

=SUMX(SUMMARIZE(VALUES(Table1[Acc #]),[Acc #],"ABCD",MIN(Table1[Value])),[ABCD])

Hope this helps.

Answer 2

Try using a finite range instead of entire columns, e.g.

=SUMPRODUCT(IFERROR(1/COUNTIFS(AUM!$C$2:$C$100,$A484,AUM!$F$2:$F$100,AUM!$F$2:$F$100),0),AUM!$K$2:$K$100)

This works for me. (Don't forget to confirm the formula with Ctrl+Shift+Enter)

Answer 3

I want to avoid a helper column b/c we are pulling the data and dumping into the sheet. 

HansV - I tried the formula above and enter this on mine : 

=SUMPRODUCT(IFERROR(1/COUNTIFS(AUM!C:C,$A484,AUM!F:F,AUM!F:F),0),AUM!K:K)

This returned a 0. 

The account numbers are in F. 

I also tried this on another person that does not have duplicates and it returned a 0.

Answer 4

The solution with a helper column is very nice, but if you want to avoid that, you can use the following array formula, confirmed with Ctrl+Shift+Enter:

=SUMPRODUCT(IFERROR(1/COUNTIFS(AUM!$C$2:$C$30,$A484,AUM!$D$2:$D$30,AUM!$D$2:$D$30),0),AUM!$K$2:$K$30)

I have assumed that the account numbers are in column D of the AUM sheet. Adjust the ranges as needed, but I'd avoid using entire columns for reasons of performance.

Answer 5

If you are open for Helper column, job will be very easy...

Use below formula (You need to change cell references)

=SUMIFS($C$2:$C$10,$D$2:$D$10,1,$A$2:$A$10,F1)

Formula in D2 for helper column is

=COUNTIF($B$2:$B2,$B2)