Unexpected formula error

I tried breaking it down into smaller segments prior to posting which I should have included. I just went around the problem by joining a table.