Thank you for the reply.

I changed my original formula in two ways:

1. =IF(K2<>"Medical","",SUBSTITUTE(TRIM(IF(COUNT(SEAR CH({ "A","B","C","D","E"},D2)),10,"") &IF(COUNT(SEARCH({"F","G","H","I"},D2)),5,"") &IF(COUNT(SEARCH({"J","K","L","M"},D2)),7,"")), " ",","))
1a. This now gives blanks if column K if NOT = "Medical" - Good
1b. This now gives the proper number output in M, but the #'s 10, 5, or 7 are in TEXT form as they are shifted to the left side of the M column cells.
1c. This formula does NOT allow me to add up column M when finished with all entries; it gives a 0.00 result (End of column is just =SUM(M2:M2034))

So then added the following as you noted:

2. =--IF(K2="Medical","",SUBSTITUTE(TRIM(IF(COUNT(SEARCH ({ "A","B","C","D","E"},D2)),10,"") &IF(COUNT(SEARCH({"F","G","H","I"},D2)),5,"") &IF(COUNT(SEARCH({"J","K","L","M"},D2)),7,"")), " ",","))
2a. This outputs the proper NUMBER (10, 7, or 5) if "Medical" in column K - good
2b. This also gives a #VALUE output in Column M if column K is not "Medical" - Bad.
2c. Then as expected the Sum of column M = #VALUE


I then tried the following (added a zero between ""):

3. =--IF(K2="Medical","0",SUBSTITUTE(TRIM(IF(COUNT(SEARC H({ "A","B","C","D","E"},D2)),10,"") &IF(COUNT(SEARCH({"F","G","H","I"},D2)),5,"") &IF(COUNT(SEARCH({"J","K","L","M"},D2)),7,"")), " ",","))

3a. This gives a 0.00 valve for every M column cell that does not have "Medical" in Column K. **This is fine, it just doesn't look very clean.
3b. All column M results are correct (10, 7, or 5) if K="Medical" and if Column D has a value listed.
3c. However, if column K = Medical, and column D does not contain one of the values (A-M from the example) then I get a #VALUE. (IN TURN I get a total showing #Value)

Overall these are the closest results I have, and in case 3 it is not bad to get the #VALUE listed as it tells me that company is not in my A-M list within the formula, and that I need to add it.

Not ideal, not real clean looking, but it is working for now.

Let me know if you have any other ideas, and if not then that's okay also. I'll just keep plugging away at it over time.
Thanks for the help, you got me on a better path.