How to SUM() rows in current column while using single array formula per row?

Question

I have the feeling I'm missing something important.

What I'm trying to do should be simple.

I have calculations on rows for 31 days. I'm using array formula on each row to do a 'baby lock', so that user can't overwrite single formula, but has to either change the whole row or nothing.

For some reason, I'm not able to SUM() only current column for each day.

This is what I'm trying to achieve, row 5 should be array from day 1st to ...:

Table draft: https://drive.google.com/open?id=1l2rGi-56YoU6ddGX2GgnS3RwMPETl-FZ

I tried:

SUM(2:3) entire row (no columns)
SUM(B2:E3) the range of columns equal to the array formula columns
SUM(B2:B3) rows in current column

put =SUM(B2:B3) in B5 and drag/copy it over the other columns. — Scott Craner
– Scott Craner, Commented Jan 18, 2019 at 16:05
@ScottCraner Scott, this does work, but it doesn't protect the row as a whole, single array formula - I was aiming for either edit all or none, on a per-row basis, with no sheet protection or macro (keeping it as simple as possible). Many thanks — Filcuk
– Filcuk, Commented Jan 18, 2019 at 16:15
You can't in Excel with a formula. Each cell that returns a value must have a formula in it. You can use vba that will take the formula in A5 and apply it to the whole range and protect it using a worksheet change event. But formulas will not get you what you want. — Scott Craner
– Scott Craner, Commented Jan 18, 2019 at 16:19
Well you can highlight B5:E5, put =INDEX(B2:E2+B3:E3,) in the formula bar and hit Ctrl-Shift-Enter. — Scott Craner
– Scott Craner, Commented Jan 18, 2019 at 16:23
Scott, apologies if I'm not expressing myself properly. I'm trying to use multi-range array formula: — Filcuk
– Filcuk, Commented Jan 18, 2019 at 16:27

Scott Craner · Accepted Answer · 2019-01-18 17:05:43Z

2

I have solved the problem.

Formula for below table in A4 looks like this:

={A2:C2+A3:C3}

(Ctrl+Shift+Enter of course)

Table

I think SUM() doesn't work because it already treats it's input as an array, but I'm not certain.

edited Jan 18, 2019 at 17:05

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

answered Jan 18, 2019 at 16:30

Filcuk

1841 silver badge18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to SUM() rows in current column while using single array formula per row?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related