# simplify redundant information

Is there a way to have the formula eliminate the redundancy. Can i specify the set of unique cells in a grouping and have the redundant part simplify to one line and have it repeat to all the unique cells. Like an array but perhaps a reverse array

=ArrayFormula(SUM( IF(OFFSET( \$D\$97,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/-6),0,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/ 6)) <>"", OFFSET( \$F\$97,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/-6),0,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/ 6))) + IF(OFFSET( \$Y\$97,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/-6),0,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/ 6)) <>"", OFFSET(\$AA\$97,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/-6),0,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/ 6))) + IF(OFFSET(\$DZ\$97,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/-6),0,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/ 6)) <>"", OFFSET(\$EB\$97,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/-6),0,ROUNDDOWN(COUNT(\$LD\$3:\$LD\$96)/ 6))))

the unique cells are he ones just right of the offset function

• It is hard to reverse engineer your formula to understand what you are trying to do. Please edit your question and insert a table of sample data side-by-side your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Jan 12 at 10:13

Edit 2023-04-14

Use let() to specify each sub-operation as a closure defined through a lambda function, and apply those closures to your data, like this:

``````=let(
offsetRows, rounddown(count(LD3:LD96) / -6),
numRows, rounddown(count(LD3:LD96) / 6),
offset_, lambda(r, offset(r, offsetRows, 0, numRows)),
sparse_, lambda(a, b, if(offset_(a) <> "", offset_(b), 0)),
arrayformula(sum(sparse_(D97, F97) + sparse_(Y97, AA97) + sparse_(DZ97, EB97)))
)
``````

Before the `let()` function became available in Google Sheets, you had to use a "naked", `lambda()`, like this:

``````=arrayformula(
lambda(
offsetRows, numRows,
sum(
if(
offset(D97, offsetRows, 0, numRows) <> "",
offset(F97, offsetRows, 0, numRows),
0
) +
if(
offset(Y97, offsetRows, 0, numRows) <> "",
offset(AA97, offsetRows, 0, numRows),
0
) +
if(
offset(DZ97, offsetRows, 0, numRows) <> "",
offset(EB97, offsetRows, 0, numRows),
0
)
)
)(
rounddown(count(LD3:LD96) / -6),
rounddown(count(LD3:LD96) / 6)
)
)
``````