Need to Identify Blank Cell Locations, Index them, and Assign them values

Hello

Everyone was of great help last time I posted and I appreciate your efforts.

Today I am working with some cost data and I need to do a few things

1) Use a formula to count and identify which blank cells are being counted (Or cells that contain Y, more on that later). I have the number but need to be able to identify which of the cells are blank
2) Once identified, Need to have them display or be able to assign them a value in a key
3) Add the number of missing key values together to get a total cost

On the attached sheet, I have a list of agents, missing states, and the cost per state. Ignoring the X's, I need to count the blank cells per line and identify the total cost per agent. Ex. Agent 1 has 5 missing states shown from the formula =COUNTIF(D2:BB2,""). I need a way to identify which of those 5 states are the ones missing and assign each of them to a key that has a specified cost. Once that is done, be able to add the costs together to get Total cost. I need to work with row 32 "Cost per State". Not sure if I am going to use Total cost in row 34 yet so you can probably ignore. Also Not really sure where to start with the Key so by all means please enhance or if you have any advice I would appreciate the help.

This is a snippet from a real data set so the X's are states these particular agents don't need. I would like to have a way or an idea for a key I can use to mix and match different states so I can test 2 or 3 or 15 states to see what the total cost would be. Ex. If I'm looking at the cost of NY and OH, being able to add "Y" in the state column and have it pull the value of that state from the key into the equation. Whatever state has a Y would add the state to the equation and total cost would be something like If Y, reference the key below and pull that value and add any other states that have Y

I added some additional notes and comments to the sheet to explain what I am trying to do. Please let me know if it needs more explaining.

As always, appreciate your time and help

Added a function called ConcatenateIf to the worksheet and used it in column BD to display missing states.

For Total cost, wouldn't =SUMPRODUCT(--(D2:BB2="");$D$32:$BB$32) be okay? Instead of keys you could maybe add the cost directly?

Thanks Mrrrr. This worked!

I have a few follow up questions if you don't mind.

On your concatenateif formula, you have it showing as comma + space. I don't see you entering " " in the formula. When I try to recreate it it is listed as AL,CO,IN without the spaces. if I add " " anywhere it won't make the formula work either. Just curious how you were able to add them in

Also in =SUMPRODUCT(--(D2:BB2=""),$D$32:$BB$32) What does the -- do here? I assume D2:BB2="" is looking for the blank cells and then lining up the black cell to the corresponding $$ amount in row 32. D$32:$BB$32

Thanks!