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/

 

Leave a Reply

Your email address will not be published. Required fields are marked *