How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6116
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I'm having an issue where when I try to use

Customer Question

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 re-adds 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.
Submitted: 3 months ago.
Category: Microsoft Office
Expert:  Jess M. replied 3 months ago.

Hi Customer, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

I am very sorry about your issues. Can you send me a copy or a dummy of your problem Excel file where this problem is happening so that I can check it on my PC? You can delete sensitive information before adding the file here in your reply or in attaching it in your reply.

Please let me know by replying to me here so that I can help you further.

Best regards,
Jess

Customer: replied 3 months ago.
Sorry for the delay, it's definitely an issue with the sheets. Copying cells over to a new spreadsheet doesn't replicate the problem. But I copied two sheets over for you.
Expert:  Jess M. replied 3 months ago.

Thank you for the test or sample files. Please give me a moment to check it.

Expert:  Jess M. replied 3 months ago.

Thank you for patiently waiting. I just fixed the problem on this particular sample file. It appears that the sheet names have special characters or hidden characters that caused the 3D sum or 3 dimensional sum formula to fail.

In order to fix your issue, the sheet range needs to be enclosed with single quotes, like:

=SUM('N75736:N75737'!H16)

I created sample sheets and the format sum(sheet1:sheet2!A1) works fine. So I believe the problem is with the named sheets that you have. I believe these sheets are created by other programs and not created manually so that explains why the naming has issues.

Here is the attached fixed file:

http://filesxpress.com/d-50c7fbde

Another thing that can surely help you in your 3D sum formula is by using wildcards. In your sample file, you can enter the formula:

=SUM('N75*'!H16)

And when you press Enter, Excel will build the correct formula for you. This works ONLY if the names of the sheets are in continuous numbering like your, N75736 and N75737.

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!

If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.

Thank you!

Best regards,
Jess

Related Microsoft Office Questions