how to calculate amount using multiple values or percentage (with ranges.)

Use VLOOKUP for ranging

for sum of weighted ranges you may use SUMPRODUCT

see attached

I've assumed that Category/Score/Range/Weight/WeightScore are in columns A/B/C/D/E, with data starting in row 2.

To get the 'range' automatically from the 'score', use this:

Formula:

Please Login or Register  to view this content.

If you also need the 'weight' automatically from the 'category', use this:

Formula:

Please Login or Register  to view this content.

(make sure you format the column as 'percentage' to have 0.4 display as 40%)

Then the 'weightscore' is a simple multiplication:

Formula:

Please Login or Register  to view this content.

and the total score is, obviously, a simple sum:

Formula:

Please Login or Register  to view this content.

Depending on your regional settings, you may need to replace decimal points with commas (0.4 → 0,4) and commas with semi-colons ( , → ; ) in the formulae above.

Here's a file showing the formulae working: INDEX_MATCH for scores_ranges_weighting _ for pengshue.xlsx

Using the example that you provided:
Enter in C2 and fill down:

Formula:

Please Login or Register  to view this content.

Enter in D2 and fill down:

Formula:

Please Login or Register  to view this content.

Enter in E2 and fill down:

Formula:

Please Login or Register  to view this content.

Result:

	A	B	C	D	E
1	Category	Score	Range	Weight	WeightScore
2	A	82	4	40%	1.6
3	B	85	5	30%	1.5
4	C	76	3	20%	0.6
5	D	90	5	10%	0.5