It would help if we knew the values of the variables were but Evaluate will only work with strings up to 255 chars in length - I suspect your first formula exceeds that length once the variables are in place.

To check -- add a Debug.Print prior to the Evaluate to see the final formula as it will be executed (Immediate Window)

debug.Print "SUMPRODUCT((dType=""" & pType & """)*(dStage=" & pStage & ")*(dRoleType=""" & pRole & """)*(dActivity=""" & pActivity & """)*(dCategory=""" & cCat & """)*(dBand=1)*dResource)"
(use LEN etc to ascertain length thereof)