Big formulas no longer letting me add - getting 'SYNTAX OF NAME NOT CORRECT'

I have two spreadsheets, spreadsheet one has many tabs and the data on it and spreadsheet two is the 'Dashboard' spreadsheet that is pulling the data from spreadsheet 1. I've built out the formulas (a lot of if and sumifs) to give me the data I need and they work correctly. In that formula box of an already working formula, if I simply do '+' and add in a cell from spreadsheet 1 (doesn't have any complex formula) I'm getting a 'The syntax of this name isn't correct' error. But it is correct, i know this to be 100% true. Is the formula too big and this is why I'm getting errors? I tried contacting online support, they could barely spread english and I couldn't help me. This project is due for work tomorrow and when it's almost done is when I'm getting these errors.

I would like someone to screenshare and call me to go over the solution if possible.