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 a specific case I'm having trouble with.

 

 

Looking to build a formula to do the following:


Search for the ID number on the master list ONLY IF:
On column [Repeated?] it has FALSE, AND on column [MODE] it has anything other than "COLLECT" / Otherwise return a 0.
-If found, retrieve the COST (if the number repeats then sum all costs for matching ID)
-If the ID is not on the master list then the cost must be calculated:
Sum the weight on all matching IDs and multiply that for the corresponding RATE matching the MODE

 

I'm showing what I have built so far, and the cost calculation works but cannot make the highlighted part work/not sure how to add it.

=IF(AND(E2=FALSE,C2<>"collect"),XLOOKUP(B2,L:L,M:M,XLOOKUP(C2,I:I,(J:J)*((SUMIF(B:B,B2,D:D))),FALSE,0,1),0,1),0)

 

Also, I would like to implement a minimum for below cases:
For GROUND IDs total cost may not be below 5.28, if the calculation falls below that it must return 5.28
For LTL IDs total cost may not fall below 86.11 so it must return 86.11 if it does.

 

I'm open to all suggestions as well as questions if something is not clear enough.

 

Thanks in advance!!

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

     

15 Replies

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

  • mathetes's avatar
    mathetes
    Silver Contributor
    Aug 26, 2021

    Since it's a dummy file to begin with, it would be far more helpful to post that dummy file than just an image of it. That way we could work with the file rather than having to re-create it.

    What version of Excel do you have? Are you able to use the new functions like UNIQUE, or are you identifying the duplicates ("repeated") manually? The most recent version of Excel is needed for that function (and a few others) to operate.

    • Ccrossed's avatar
      Ccrossed
      Copper Contributor
      Aug 26, 2021
      Agreed, I have attached dummy file, also i'm currently using Office 365 for enterprise.
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        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)
)

         

Resources