SUM(INDIRECT(“‘”&B3&”‘!C5:C100”))
explanation:
“‘” –> an apostrophe (red) between the quotes – to handle sheet names that contain space(s)
& –> to concatenate
B3 –> cell that contains the sheet name
& –> to concatenate
“‘!C5:C100” –> apostrophe; exclamation mark ! ; range
Ref: https://www.mrexcel.com/board/threads/sum-with-sheet-names-as-a-variable-instead-of-manually-provided-text.1027982/