Excel formula to find total price based on the range of users

New Contributor


I am trying to calculate a price matrix per user based on the number of users. 

If there are 5 users, the price for each of 5 users is 20
If there are 8 users, the price for 1st 5 users is 20, then for the rest 3 users is 18. The total will be the sum of both


It seems tricky for me to calculate if there are more than 5 users, as prices vary based on the number of users. If there are 20 users, there is a need to consider 4 price ranges and so on. 


Any help will be highly appreciated


2 Replies
best response confirmed by Sergei Baklan (MVP)


See the attached version.

I have a query. I see that the equation works, but when I insert 6 instead of giving me the sum of 118, I get a sum of 120. the same is applicable when I insert 7,8.  But when I insert 10,20,30 etc I get the correct sum.


I am sorry that, yet to understand complete logic


Thanks a lot for your help