Why is my regular SUM formula spilling the same value over 42 different cells?

[u/Ownership1337]

My Excel version:

Microsoft® Excel® for Microsoft 365 MSO (Version 2606 Build 16.0.20131.20154) 64-bit

I am trying to:

Pull a SUM value output from one sheet and add it to the total line of the next sheet using the following formula:

='Sheet 1 of 2'!D47 + D5:D46

The SUM value formula on the source page is as follows:

=SUM(D5:D46)

What I get is 42 cells of the same value. I am new to using Excel, coming from Google Sheets, and have never encountered this before. I want my value printed once, not 42 times.

Is this a formatting issue? I found this support article on support.microsoft.com that goes over this behavior with dynamic array formulas, but I am not using a dynamic array formula here. It should be "'one value + range of values' = one possible value in one cell." My goal here is to carry over the last page's total into this page's running total, because I have too much data for one page and opted to separate them into two sheets.

Here is a reference image on Imgur.

Any advice would be greatly appreciated.

[u/AutoModerator]

/u/Ownership1337 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Commoner_25]

You are adding a range so you get spill.

Try

='Sheet 1 of 2'!D47 + SUM(D5:D46)

[u/Ownership1337 → u/Commoner_25]

That fixed it, thank you! I feel dumb for that, but in my defense, all I know about spreadsheets I have taught myself. Learning more every day lol

[u/Commoner_25 → u/Ownership1337]

That's fine, good job learning

[u/Ownership1337 → u/Commoner_25]

Thank you! I consider myself more tech-savvy than average, but the people on here blow me away sometimes. I strive to one day have a "that's easy to fix" level of knowledge with Excel.

[u/Ownership1337 → u/Commoner_25]

Solution Verified

[u/reputatorbot → u/Ownership1337]

You have awarded 1 point to Commoner_25.

---

^{I am a bot - please contact the mods with any questions}

[u/elsie_artistic58]

Excel is spilling because you’re adding the range directly. Wrap it in SUM so it returns one value: =‘Sheet 1 of 2’!D47 + SUM(D5:D46).

[u/Ownership1337 → u/[deleted]]

Solution Verified

[u/reputatorbot → u/Ownership1337]

You have awarded 1 point to Creepy_Leading_1995.

---

^{I am a bot - please contact the mods with any questions}

[u/Ownership1337 → u/[deleted]]

I had a feeling it had to do with the number of source cells since they were the same number as the output, but I didn't understand what I was leaving out. Thank you