Sumproduct find total price based on the range of users

New Contributor

I am trying to calculate a price matrix per user based on the range of users and price against range
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 18. 

I was helped by a community member to solve the issue using "SUMPRODUCT". But when I insert numbers like 1,2,6,7,11,12 etc the formula provided doesn't work as intended




The formula used is =SUMPRODUCT((B14>$B$3:$B$8+1)*(B14-$B$3:$B$8+1),$E$3:$E$8)


I am unable to understand the logic and what is wrong with the logic provided 

Any help will be highly appreciated

2 Replies
best response confirmed by BBS90 (New Contributor)


See the attached version.

Thanks a lot, @hans. It works