sum but ignore existing concatenated criteria

btardo01

Board Regular
Joined
Oct 6, 2009
Messages
160
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 ACoverage 1South10.0045.00
Client ACoverage 2South15.0045.00
Client ACoverage 3South12.0045.00
Client ACoverage 4South8.0045.00
Client ACoverage 1South10.00(blank)
Client ACoverage 2South15.00(blank)
Client ACoverage 3South12.00(blank)
Client BCoverage 1South5.0012.00
Client BCoverage 2South7.0012.00
Client BCoverage 15.00(blank)
Client ACoverage 1North13.0020.00
Client ACoverage 2North7.0020.00

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Oct 6, 2009
Messages
160
I forgot to mention, If possible to do without an array, that would be best.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top