Forum Discussion

ishaqib's avatar
ishaqib
Copper Contributor
Mar 21, 2023

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 ... 

 

  • kumaraveljd's avatar
    kumaraveljd
    Copper Contributor

     

    Can any one help me with creating a formula for below type of incentive structure.

     SLAB/LAKHSMINIMUM ELIGIBLE NOMINIMUM ELIGIBLE VOLUMEINCENTIVE
    SLAB 13002103000
    SLAB 24503156750
    SLAB 365042013000
    SLAB 475052518750
    SLAB 585063025500

     

     

     

    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 

  • ishaqib 

    =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.

     

     

Resources