I ran into a similar issue yesterday. Solution was:
=SUM(IMPORTRANGE("SpreadsheetURL","SheetName!A1:A100"))
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
#NULL!
#DIV/0!
#VALUE!
#REF!
#NAME?
#NUM!
#N/A
then your formula must be modified to include SUMIF()
as follows
=SUMIF(IMPORTRANGE("SpreadsheetURL","SheetName!A1:A100"), ">0")
This ensures that it's only adding actual numbers and not error codes.