Calculating a sum based on multiple user input (but defined) criteria

[Chrizh]

Hello! Thanks in advance for any help provided

I have attached an example spreadsheet which should hopefully provide the information.

In brief, I would like for the user to be able to select from defined lists in B4, C4 and D4 and based on their selections an answer is provided in E4 (and F4, but for now if concentrate on E4 first, F4 will work similar fashion).

To elaborate. This is for a mobile game called SpotRacers. The user has vehicles in their garage which have a 'rating' between 1* and 5*. The vehicle also has a 'level' starting at level 1. To increase levels, the user must spend 'tools' (and 'parts', but ignore for now). The amount of 'tools' required to level up is determined by the vehicles 'rating'. 1* rated vehicles cost less tools to upgrade then 5* vehicles. There is an increasing scale for the amount of tools based also on the vehicles current level. Levelling up from 1 to 10, the increments are set at a certain number. Levelling up from 11 to 20 that increment is a set number but higher. And likewise for levelling up from 21 to 30. It's simple maths in the sense of:

1* vehicle.
Level 1 is zero.
Level 2 costs 200 tools.
Levels 1-10 are at increments of 200 tools.
Levels 11-20 are at increments of 400 tools.
Levels 21-30 are at increments of 600 tools.

2* vehicle.
Level 1 is zero.
Level 2 costs 200 tools.
Levels 1-10 are at increments of 400 tools.
Levels 11-20 are at increments of 800 tools.
Levels 21-30 are at increments of 1200 tools.

And so on. This is all in the spreadsheet.

So B4 has a drop down:

*
**
***
****
*****

This is the vehicle 'rating'.

C4 and D4 have drop downs containing list of numbers from 1 to 30. These are the 'levels'.

C4 is the vehicle's current 'level'. D4 is the desired 'level'.

E4 is the 'tools' cost to level up between the levels in C4 and D4 (considering 'rating' in B4).

I'm sure that I need some sort of VBA script to achieve what I am trying to achieve but I'm not an expert in VBA! Have tried a few formulas but I think there are too many variables for SUMIFS and IF, AND etc.

Any help would be greatly appreciated. Thank you.

[JohnTopley]

Try

in E4

=SUMIFS(INDEX($K$3:$O$31,0,MATCH($B$4,$K$1:$O$1,0)),$J$3:$J$31,">=" &$C$4,$J$3:$J$31,"<=" &$D$4)

in F4

=SUMIFS($P$3:$P$31,$J$3:$J$31,">=" &$C$4,$J$3:$J$31,"<=" &$D$4)

Changed "*" to "★" in Drop down

[Chrizh]

Try

in E4

=SUMIFS(INDEX($K$3:$O$31,0,MATCH($B$4,$K$1:$O$1,0)),$J$3:$J$31,">=" &$C$4,$J$3:$J$31,"<=" &$D$4)

in F4

=SUMIFS($P$3:$P$31,$J$3:$J$31,">=" &$C$4,$J$3:$J$31,"<=" &$D$4)

Changed "*" to "★" in Drop down

This worked absolutely perfectly. Thank you.