Forum Discussion
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.
Like
=IF(SUM()>30000,30000-G16,IF(SUM()>G16,SUM()-G16,0))
Like
=IF(SUM()>30000,30000-G16,IF(SUM()>G16,SUM()-G16,0))
- Detlef_LewinSilver Contributor
Alex,
for calculating commission you should have a look at Mike Girvins YouTube channel. He has done some videos about the topic.
- Alex MartinezCopper Contributor
He has some interesting things on his channel. Thanks for the heads up!