sum but ignore existing concatenated criteria

[btardo01]

I have client in column A. Coverage in column B. Location in column C and Income in column D. I'm hoping someone can help with the formula for column E. In column E, I want to sum everything for Client A as long as the combination of Client&Coverage&Location doesn't already exist. If that combination already exists then I would want to leave blank.


[TABLE="width: 300, align: left"]
<tbody>[TR]
[TD]Client A[/TD]
[TD]Coverage 1[/TD]
[TD]South[/TD]
[TD]10.00[/TD]
[TD]45.00[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Coverage 2[/TD]
[TD]South[/TD]
[TD]15.00[/TD]
[TD]45.00[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Coverage 3[/TD]
[TD]South[/TD]
[TD]12.00[/TD]
[TD]45.00[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Coverage 4[/TD]
[TD]South[/TD]
[TD]8.00[/TD]
[TD]45.00[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Coverage 1[/TD]
[TD]South[/TD]
[TD]10.00[/TD]
[TD](blank)[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Coverage 2[/TD]
[TD]South[/TD]
[TD]15.00[/TD]
[TD](blank)[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Coverage 3[/TD]
[TD]South[/TD]
[TD]12.00[/TD]
[TD](blank)[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Coverage 1[/TD]
[TD]South[/TD]
[TD]5.00[/TD]
[TD]12.00[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Coverage 2[/TD]
[TD]South[/TD]
[TD]7.00[/TD]
[TD]12.00[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]Coverage 1[/TD]
[TD][/TD]
[TD]5.00[/TD]
[TD](blank)[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Coverage 1[/TD]
[TD]North[/TD]
[TD]13.00[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]Coverage 2[/TD]
[TD]North[/TD]
[TD]7.00[/TD]
[TD]20.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[btardo01]

I forgot to mention, If possible to do without an array, that would be best.

 

[btardo01]

Any ideas on this one?

thanks

 

[PaddyD]

You could do it all in one formula but it would be computationally expensive.

Why not just do the concatenation directly in the sheet and work off that. So, e.g., if you added the concatenated string to new column F, something like:

=IF(COUNTIF($F$2:F2,F2)=1,SUMIF($F$2:$F$10,F2,$E$2:$E$10),"")

...in g2 copied down