How to fix formula result when adding non-consecutive sheets

Question

I am trying to add non consecutive sheets in excel with this formula:

+'C:\Users\brian\Downloads[Project 230 Tracker - BR.xlsx]IPT (LI) Construction-VT'!B3+'C:\Users\Downloads[Project 230 Tracker - BR.xlsx]IPT (MA) Construction-VT'!B3+'C:\Users\Downloads[Project 230 Tracker - BR.xlsx]C3 Construction-VT'!B3+'C:\Users\Downloads[Project 230 Tracker - BR.xlsx]DPS Construction'!B3+'C:\Users\Downloads[Project 230 Tracker - BR.xlsx]KCP Construction'!B3+'C:\Users\Downloads[Project 230 Tracker - BR.xlsx]MSS (Cape) Construction'!B3+'C:\Users\Downloads[Project 230 Tracker - BR.xlsx]MSS (Diggins) Construction'!B3+'C:\Users\Downloads[Project 230 Tracker - BR.xlsx]PEC Construction'!B3

and it is returning this value: 370808

instead of the actual two digit total=82?

Answer 1

Hi @Brian Detwiler,

Welcome to the Microsoft Q&A forum.

Thank you for contacting us. I would like to provide you with the following information:

Based on your description, the issue is not related to summing across sheets, but rather to the data in the B3 cells. Specifically, the value in cell B3 may be formatted as text instead of a number.

In this case, please use the following function to check the data in the B3 cells:

Step 1: Select any cell

Step 2: Enter the following formula:

=ISTEXT(B3)

• If the result returns FALSE, the data in cell B3 is numeric
• If the result returns TRUE, the data in cell B3 is text

Additionally, please make sure that the B3 cells are set to the Number or General format, not Text.

To check this, click on cell B3, press Ctrl + 1, then select Number or General.

I hope the information above is helpful. Please follow the steps and let me know the result. If the issue is still not resolved, we can continue working together to troubleshoot it.

Thank you for your patience and understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you.

I look forward to continuing the discussion.

---

 If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

The issue you are experiencing with the formula returning an unexpected value (370808 instead of 82) could be due to several factors, including the way Excel handles references to non-consecutive sheets or potential formatting issues with the cells being referenced.

Here are a few steps you can take to troubleshoot and potentially fix the formula:

1. Check Cell Formatting: Ensure that the cells you are referencing (B3 in each of the sheets) are formatted as numbers. If they are formatted as text, Excel might not sum them correctly.
2. Use SUM Function: Instead of adding each cell individually, consider using the SUM function, which can handle multiple references more efficiently. For example:

      =SUM('C:\Users\brian\Downloads\[Project 230 Tracker - BR.xlsx]IPT (LI) Construction-VT'!B3, 'C:\Users\brian\Downloads\[Project 230 Tracker - BR.xlsx]IPT (MA) Construction-VT'!B3, 'C:\Users\brian\Downloads\[Project 230 Tracker - BR.xlsx]C3 Construction-VT'!B3, 'C:\Users\brian\Downloads\[Project 230 Tracker - BR.xlsx]DPS Construction'!B3, 'C:\Users\brian\Downloads\[Project 230 Tracker - BR.xlsx]KCP Construction'!B3, 'C:\Users\brian\Downloads\[Project 230 Tracker - BR.xlsx]MSS (Cape) Construction'!B3, 'C:\Users\brian\Downloads\[Project 230 Tracker - BR.xlsx]MSS (Diggins) Construction'!B3, 'C:\Users\brian\Downloads\[Project 230 Tracker - BR.xlsx]PEC Construction'!B3)
   

3. Check for Errors: Ensure that there are no errors in the cells being referenced. If any of the cells contain an error (like #VALUE!), it could affect the total.
4. Manual Calculation: If the formula still does not yield the correct result, manually check the values in each referenced cell to ensure they are what you expect.
5. Recalculate Workbook: Sometimes, Excel may not recalculate automatically. You can force a recalculation by pressing Ctrl + Alt + F9 to recalculate all formulas in the workbook.

If these steps do not resolve the issue, you may need to further investigate the specific values in the referenced cells or consider whether there are any hidden sheets or data affecting the calculations.

---

References:

• Working with formulas
• Evaluating names and other worksheet formula expressions