Forum Discussion
Solved
Sum of incentive amount based on the cell values
Hey there, I m trying to calculate the total sum of incentive amount in term of models. I am attaching a screenshot where you easily understand things. In table 1 , These are the models no ...
We may do step by step
In first step we return the matrix where each column corresponds to each model in Table1. Row indicates if such model is in related row of Table2 in Models. We use double dash "--" to convert TRUE and FALSE to 1 and 0 accordingly.
On second step we MMULT above matrix with incentives column of Table1. MMULT multiplies values in each row of matrix on related incentives value and return the sum for the row (first column matrix value * first row incentives value + second column matrix value * second row incentives value).
On third step we generate current name filter on Names in Table2.
Finally multiply one on another and sum resulting vector to have total sum for the current name.
Thank you for your response sir, For now, i got my answer but didn't understand how this formula work
We may do step by step
In first step we return the matrix where each column corresponds to each model in Table1. Row indicates if such model is in related row of Table2 in Models. We use double dash "--" to convert TRUE and FALSE to 1 and 0 accordingly.
On second step we MMULT above matrix with incentives column of Table1. MMULT multiplies values in each row of matrix on related incentives value and return the sum for the row (first column matrix value * first row incentives value + second column matrix value * second row incentives value).
On third step we generate current name filter on Names in Table2.
Finally multiply one on another and sum resulting vector to have total sum for the current name.
HeySergeiBaklan I need your little help, It was running good but there should be a condition that if a salesperson sell a model with lower of its price then no incentive will be given, we have to ignore that sales. How can we do that? I tried to use sumif and in last i gave condition that sale amount >= transpose of incentive model price amount but it give me error
ishaqib
I'm not sure where and how do you define prices. If like thisformula could be
=SUM( MMULT( --(Table2[Model] = TRANSPOSE(Table1[Model])), Table1[Incentive]) * ( MMULT( --(Table2[Model] = TRANSPOSE(Table1[Model])), Table1[Price]) <= Table2[Actual price] ) * (Table2[Name] = [@Name]) )
- Thankyou so much sir for this clarification
