Sum + Xlookup issues!!!

[MarkReddell]

Hello XLers,

I'm having issues with combining the sum function with xlookup is this formula: = IF( AND( COUNTIFS( ContractsTable[@[Lead]:[Production]], BH$4)>0, [@[Dept'#]]=6), SUM( XLOOKUP([@Date], CommissionsTable[Date], XLOOKUP(BH$4,$AZ5:$BE5, CommissionsTable[[Lead]:[Production]],,-1),,-1)), "")

I need to lookup my salesman listed starting in Column "BH$4" (across row 4) in a list of salesman listed in: $AZ5:$BE5. This list is the work levels that I'm trying combine total commissions per salesman. The return commission levels are listed in: CommissionsTable[[LEAD]:[PRODUCTION}}. Which are 6 levels that are from 25%, 15%, 25%, 20%, 10%, & 5% totaling 100%. My problem with this formula is that its only returning the first match & not the sum across ALL 6 commission levels.

Please if there is any ideas I would be very thankful!!!








= IF( AND( COUNTIFS( ContractsTable[@[Lead]:[Production]], BH$4)>0, [@[Dept'#]]=6), SUM( XLOOKUP([@Date], CommissionsTable[Date], XLOOKUP(BH$4,$AZ5:$BE5, CommissionsTable[[Lead]:[Production]],,-1),,-1)), "")

 

[jasonb75]

Xlookup is definitely not the correct function for what you are attempting to do, I would guess that you might need either sumif with offset or sum with filter but without a visual representation of your data and expected results (XL2BB please, not screen captures) there would be too much guesswork involved in trying to resolve this.