Forum Discussion

Ccrossed's avatar
Ccrossed
Copper Contributor
Aug 26, 2021
Solved

Help with a big formula using and, xlookup, if and sumifs

  Hello all! I'm working on a report (per company policy can't share) on which I would like to optimize with formulas. I made a dummy example as close as I could so I could explain myself better for...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 26, 2021

    Ccrossed 

    If I understood the logic correctly that could be

    =LET(
  getSum, IF( ISNA( XMATCH([@ID],MasterList[Master list]) ),
          XLOOKUP([@MODE],
                   Rates[MODE],
                   Rates[RATE]*SUMIF([ID],[@ID],[WEIGHT]),
                   FALSE,0,1),
          SUM( FILTER( MasterList[Cost], MasterList[Master list] = [@ID]) )
       ),
 adjustedSum, IFS( [@MODE] = "Ground", MAX( 5.28, getSum),
                   [@MODE] = "LTL", MAX( 86.11, getSum),
                   TRUE, getSum),
 IF( AND( [@[Repeated?]]=FALSE,
          [@MODE]<>"collect"),
     adjustedSum,
     0)
)

     

Ccrossed's avatar
Ccrossed
Copper Contributor
Aug 30, 2021
Thank you Sergei, it seems to be doing its job. One thing tho, would it be possible to adapt this formula from a table to a range? Original document is continuously growing with now more than 2k rows and each of the tables here are on separate tabs. Thanks!
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 30, 2021

Ccrossed 

You may but I see no one reason to do that. Structured table automatically add formulas to new rows. It doesn't matter in which sheet you place the table, only table name is used.

Thus you don't need enter formulas to new rows, just add your data and table does the rest.

 

However, you may stay on the table, convert it to the range from menu and formula will be adjusted automatically. Reference on entire column in formulae is always not a good idea, obvious side effect is performance. To improve it you may use dynamic ranges like

=LET(
  masterListID,    $L$2:INDEX($L:$L,COUNTA($L:$L)),
  masterListCost, $M$2:INDEX($M:$M,COUNTA($M:$M)),
  mode,           $I$2:INDEX($I:$I,COUNTA($I:$I)),
  rate,           $J$2:INDEX($J:$J,COUNTA($J:$J)),
  ID,             $B$2:INDEX($B:$B,COUNTA($B:$B)),
  weight,         $D$2:INDEX($D:$D,COUNTA($D:$D)),
  getSum, IF( ISNA( XMATCH($B2,masterListID ) ),
          XLOOKUP( $C2,
                    mode,
                    rate*SUMIF(ID,$B2,weight),
                    FALSE,0,1),
          SUM( FILTER( masterListCost, masterListID = $B2) )
       ),
 adjustedSum, IFS( $C2 = "Ground", MAX( 5.28, getSum),
                   $C2 = "LTL", MAX( 86.11, getSum),
                   TRUE, getSum),
 IF( AND( $E2=FALSE,
          $C2<>"collect"),
      adjustedSum,
      0)
)

but in any case you shall be sure nothing is added below main data in columns you use. Please check in attached.

 

In general structured tables were made to improve calculations in cases like yours. But what to use is always your decision.

  • Ccrossed's avatar
    Ccrossed
    Copper Contributor
    Aug 31, 2021

    SergeiBaklan 

    Awesome! yes I also believe a table would be a more efficient way of dealing with the data, so I'm definitely gonna propose that change to our reports. One last thing I would like help with would be to update the formula to look into different sheets for the complementary tables, this so I can translate the formula to our main workbook as smoothly as possible. My knowledge on Excel is still limited so I'm having some trouble understanding some components on your formula.

     

    New dummy attached.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 31, 2021

      Ccrossed 

      Formula will be

      =LET(
  masterListID,   Sheet3!$A$2:INDEX(Sheet3!$A:$A,COUNTA(Sheet3!$A:$A)),
  masterListCost, Sheet3!$B$2:INDEX(Sheet3!$B:$B,COUNTA(Sheet3!$B:$B)),
  mode,           Sheet2!$A$2:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A)),
  rate,           Sheet2!$B$2:INDEX(Sheet2!$B:$B,COUNTA(Sheet2!$B:$B)),
  ID,             $A$2:INDEX($A:$A,COUNTA($A:$A)),
  weight,         $C$2:INDEX($C:$C,COUNTA($C:$C)),
  getSum, IF( ISNA( XMATCH($A2,masterListID ) ),
          XLOOKUP( $B2,
                   mode,
                   rate*SUMIF(ID,$A2,weight),
                   FALSE,0,1),
          SUM( FILTER( masterListCost, masterListID = $A2) )
       ),
 adjustedSum, IFS( $B2 = "Ground", MAX( 5.28, getSum),
                   $B2 = "LTL", MAX( 86.11, getSum),
                   TRUE, getSum),
 IF( AND( $D2=FALSE,
          $B2<>"collect"),
      adjustedSum,
      0)
)

      Please check in attached.

Resources