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

0
votes

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
1
excelexcel-formula
put =SUM(B2:B3) in B5 and drag/copy it over the other columns. – Scott Craner
@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
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
Well you can highlight B5:E5, put =INDEX(B2:E2+B3:E3,) in the formula bar and hit Ctrl-Shift-Enter. – Scott Craner
Scott, apologies if I'm not expressing myself properly. I'm trying to use multi-range array formula: – Filcuk

1 Answers

2
votes

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.