I'd recommend liberal use of the Name Manager under the Formulas tab to shorten your formulas considerably.

For example, you can set the word "Accounts" to represent: ={"HT RED","HT Halifax","Legacy Diversity","HT AURORA 1 OR 2","DATA ENTRY ROCH","HT Roch","WELLS/BANK","NYS","STORE KIT","GOVERNMENT/FED"}

Also, use Alt+Enter to organize your formulas. It will make them easy to troubleshoot and edit.

Cleaned up with linebreaks and using just three defined names you could end up with:

=SUM(COUNTIFS('Kevin Accounts'!$B$2:$B$560,">="&'Formula Data'!A2,
	      'Kevin Accounts'!$B$2:$B$560,"<="&'Formula Data'!A6,
	      'Kevin Accounts'!$C$2:$C$560,Support,
	      'Kevin Accounts'!$I$2:$I$560,Accounts,
	      'Kevin Accounts'!$J$2:$J$560,Implementation))
Which looks fine.