Sum Formula across multiple sheets

Question

Hello,

I'm having an issue where when I try to use =SUM(Sheet1:Sheet2!B2) or even =SUM(IF(ISERROR(Sheet1:Sheet2!B2),"",Sheet1:Sheet2!B2)) that I get a #VALUE! error. I'm trying to do this in a pre-existing spreadsheet. Whenever I test it in a new spreadsheet, it works.

When I go to reexamine the formula, it always switches to =SUM(Sheet1:'Sheet2'!B2) and =SUM(IF(ISERROR(J24942:'J24943'!H16),"",J24942:'J24943'!H16)), adding in the ' symbols around the second value. If I delete them, it just readds them. But this doesn't happen in a new spreadsheet.

Work has given me 500+ sheets, where I need to go through an sum 2-20 sheets of information, and I can't even get it to sum two sheets.

Answer 1

Try renaming the two sheets - use Sheet1 and Sheet2. It is possible there is a space in one of the names.

What I like to do is have two totally empty "bookend" sheets called Start and End; one before Sheet1 the other after Sheet2.

Then I use =Sum(Start:End!B2) and I can readily add more sheets between Start and End as needed.

best wishes

Answer 2

Hi Vincent,

I understand that you want to sum formula across multiple sheets.

As per your query, I suggest you to refer to the following article and check if it helps in resolvi8ng your issue:

https://blogs.office.com/2013/05/01/summing-data-across-multiple-criteria-on-multiple-worksheets/

Please post to us if you need any further assistance and we will assist you.

Thank you.