Forum Discussion
ishaqib
Mar 21, 2023Copper Contributor
Calculating the sum of incentive amount for each sales person with a given condition
Hello guys, I am stuck in a calculation. Actually, I am reporting a sheet on a daily basis recently i've added a column called incentive amount and i was calculating total sum of incentive amount for given models. It was running well but now there is a problem arise. One of salesperson sold a model which is already present in incentive sheet but in lower price, still in daily sheet it calculate incentive amount.
I am attaching a screen shot of demo data.
Let me explain you one by one- There is total three different sheet
1 sheet - sale report - where all the sales details present
2 sheet- incentive model sheet - where incentive model , minimum price to sell those model, and incentive amount
3. Daily reporting sheet - Sales person name and incentive amount
Now what I want sum of total incentive amount for each salesperson but condition is salesdetails[amount] >= Incentive sheet[amount] only if condition is satify then sum otherwise leave that cell.
I m using this formula to calculate but showing #N/A - Formula - =SUMPRODUCT((B5:B11=L4)*(C5:C11=G5:G7)*(D5:D11>=H5:H7)*(I5:I7))
Please help me to get rid out of this ...
- kumaraveljdCopper Contributor
Can any one help me with creating a formula for below type of incentive structure.
SLAB/LAKHS MINIMUM ELIGIBLE NO MINIMUM ELIGIBLE VOLUME INCENTIVE SLAB 1 300 2 10 3000 SLAB 2 450 3 15 6750 SLAB 3 650 4 20 13000 SLAB 4 750 5 25 18750 SLAB 5 850 6 30 25500 conditions:
Example
If done in volume 30 lakhs but in numbers they done 4 nos will get the SLAB 3 calculation
how to calculate above mentioned slab in both volume and number terms
pls any one help on this
- OliverScheurichGold Contributor
=VLOOKUP(C5,$G$5:$H$7,2,FALSE)<=D5
=VLOOKUP(C5,$G$5:$I$7,3,FALSE)
I'd apply two helper formulas in cells E5 and F5 and fill down as required.
=SUMPRODUCT(($B$5:$B$11=L14)*($E$5:$E$11=TRUE)*$F$5:$F$11)
This is the formula in cell M14 which is filled across range M14:M16.