1

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

1
  • 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

1 Answer 1

2

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) 
  )  
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.