CODEWITHSUNDEEP
excelformula
FishBizkit
FishBizkit

Reputation: 41

Excel adding unwanted cells to formula

I've been using Excel for quite a long time & I've never noticed this issue before. Someone at work asked me & I thought it must be a simple fix, but I'm stumped.

Let's start with A1:F1 being blanks, and G1 being =SUM(A1:C1) Pretty standard add the values. I only want G1 to sum the first 3 cells in Row1, even though there will be values in the others.

When I add values into cells A1:C1, no problem. As soon as I add a value to D1 the formula in G1 auto magically "fixes" itself to =SUM(A1:D1). Then a value in E1 changes the formula to =SUM(A1:E1). And so on... I need the formula to only sum A1:C1 regardles of the other values.

So I spent the usual 15-20 minutes looking all over the ribbons and options, and another 15-20 on the interwebs, but here I am. Hope it's not something stoopid that I missed and I feel like a dummy.

Upvotes: 2

Views: 210

Answers (2)

FishBizkit
FishBizkit

Reputation: 41

Winner Winner Chicken Dinner.

pnut solves it. "Uncheck Extend data range formats and formulas."

File> Options> Advanced > Uncheck Extend data range formats and formulas

Thank you.

Upvotes: 1

iterexet
iterexet

Reputation: 11

I'm not sure if there is a way to do that, but you could just put =SUM(A1+B1+C1) in G1 instead and then it won't do that

Upvotes: 0

Related Questions