Excel formula for adding multiple values based on criteria to be input by user

Question

I'm putting together a price list on excel MS365 version 2501 . If I enter the number of employees in a cell, I'd like the price to be calculated as follows, according to the number of employees. The price below represents the cost for that employee. So if there are 3 employees the price is £335 for employee 1 + £0 for employee 2 + £60 for employee 3 = £395. What formula will give me this?

How many employees?	Price £
1	335
2	0
3	60
4	60
5	60
6	37
7	37
8	37
9	37
10	37

Answer 1

You can use this to find the cost from your chart.

=SUM(INDIRECT("B2:"&ADDRESS(MATCH(F2,A1:A11,0),2)))

Answer 2

Hi,

In cell F2, enter this formula

=SUM(B2:XLOOKUP(E2,A2:A11,B2:B11))

Hope this helps.

Answer 3

=SUM(OFFEST(B2,0,0,F2))

Where F2 contains the number of employees.