# sum but ignore existing concatenated criteria

#### btardo01

##### Board Regular
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.

 Client A Coverage 1 South 10 45.00 Client A Coverage 2 South 15 45.00 Client A Coverage 3 South 12 45.00 Client A Coverage 4 South 8 45.00 Client A Coverage 1 South 10 (blank) Client A Coverage 2 South 15 (blank) Client A Coverage 3 South 12 (blank) Client B Coverage 1 South 5 12.00 Client B Coverage 2 South 7 12.00 Client B Coverage 1 5 (blank) Client A Coverage 1 North 13 20.00 Client A Coverage 2 North 7 20.00

<tbody>
</tbody>

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### btardo01

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

#### btardo01

##### Board Regular
Any ideas on this one?

thanks

##### MrExcel MVP
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

Replies
0
Views
46
Replies
3
Views
441
Replies
4
Views
399
Replies
12
Views
438
Replies
1
Views
155

1,109,380
Messages
5,528,366
Members
409,817
Latest member
JiNXX9500

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...