Forum Discussion
Help with a big formula using and, xlookup, if and sumifs
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) )
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.
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) )- 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!
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.
I'm not able to follow your explanation as to what you're trying to calculate. So let me send this back to you to suggest another function that might be helpful; feel free, though, to come back with clarification.
I entered this formula in cell O2: =SORT(UNIQUE(B2:C22))
to get this array of unique IDs and associated modes (UNIQUE does require the most current version of Excel, so if it doesn't work, let us know)
Beyond that, though, it's not clear to me what the relationship is between all your tables and what you're trying to calculate. I think my UNIQUE list has eliminated the need to look for FALSE in your IF function...but, as I said, it's not clear to me. I'd like to make it a more elegant solution if possible, as I'm sure you would too.
- Thanks for your reply, I know how complex this worksheet is so I'm more than glad to clarify anything possible. The master list contains a record of IDs and their cost, these can be repeated and I'm trying to get a total cost for said ID, however if any ID is not found on the master list then it should be manually calculated by multiplying a base rate (J:J) by the weight of said ID (if there are multiple matching IDs it's ok we would just have to add the total weight before getting that specific ID's total cost) However!: If and ID has "collect" on C:C and TRUE on E:E then it shall be ignored and given a 0 on total cost. Let me know if that helps!
