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

     

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Aug 26, 2021

Ccrossed 

I think there is a mistake in your data. The master list contains a duplicate.

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 26, 2021

Detlef_Lewin 

My understanding that is by design. If ID:s are repeated in Master List we calculate sum of cost for all such duplicates.

Resources