Forum Discussion

Alex Martinez's avatar
Alex Martinez
Copper Contributor
Mar 02, 2018

SUM formula help needed with multiple criteria

Having difficulty with the following:

 

IF the sum of cells G5:G10 exceed the value of cell G16, then I need to enter the sum of G5:G10 minus G16, otherwise a zero if the sum is less than G16.  This part I can do

 

=IF(SUM(G5:G10)>G16,SUM(G5:G10)-G16,0)

 

However, IF the sum of G5:G10 exceeds the value of cell G16 but is GREATER than 30000, I need then to only show the value of 30000 less G16.

 

The next total underneath this one would need be to get the sum of G5:G10 that EXCEEDS 30000.

 

For context, this is to calculate commissions that are above a min. threshold that is on a sliding scale (that's the value in G16).  All sales that exceed G16, up to 30000, are paid at one %, whereas all sales above 30000 are calculated at a higher rate.

 

 

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    Alex,

     

    for calculating commission you should have a look at Mike Girvins YouTube channel. He has done some videos about the topic.

     

    • Alex Martinez's avatar
      Alex Martinez
      Copper Contributor

      He has some interesting things on his channel. Thanks for the heads up!

Resources