SOLVED

Sum of incentive amount based on the cell values

Occasional Contributor

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.

 

Screenshot (276).png

In table 1 , These are the models no with their incentive, table 2  is my sales data sheet.

Salesman name & model will be in table 2.

Now i want to calculate the total sum of incentive amount based on model.

Please help me to get rid out of this.

I tried sumproduct with sumifs formula but getting error

thankyou in advance...

6 Replies

@ishaqib 

If In understood the logic correctly that could be

image.png

 

Hey, thanks for quick reply but that not what i want, actually i want sum of total of incentive amount in term of name

@ishaqib 

When like

image.png

Thank you for your response sir, For now, i got my answer but didn't understand how this formula work
best response confirmed by ishaqib (Occasional Contributor)
Solution

@ishaqib 

We may do step by step

image.png

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.

Thankyou so much sir for this clarification