I ran into a similar issue yesterday. Solution was:
2 important notes:
You will initially see a
#REF! error in your cell with a red color in the corner. You'll need to hover over it and allow access to the spreadsheets that you're referencing.
If any of the original data in the spreadsheet range you're referencing in
A1:A100 contain errors such as
then your formula must be modified to include
SUMIF() as follows
This ensures that it's only adding actual numbers and not error codes.