SUM FORMULA BASED ON MULTIPLE CRITERIA

KempenUSA

Help PLEASE!!!

I am looking for a formula that will sum the total travel cost of resources, if selected, based on the qty of each resource and also the resource rate (from another data sheet).

For this example I have 3 travel items:

Line 1: I have 3 resources which will be traveling, project manager, design engineer and a stress analyst.

Line 2: I have 2 resources which will be traveling, project manager and a stress analyst.

Line 3: I have 1 resources which will be traveling, design engineer.

I have a sperate sheet where I have the rates for each discipline.

The formula will be a column formula and should be based on several resource selections based on project requirements. therefore you can have any combination of resources traveling.

I have created a multi-select resource column, which based on selection will create checkboxes in the associated P1, E1 and S1 columns. These in turn generate RES-1, RES-2 and RES-3 to help me use the INDEX function for a formula based on the rate for each resource.

Can you please assist with a formula to be used in TOTAL CHARGE that will tally the total travel cost accordingly?

traveling cost.xlsx

TRAVEL DAYS	RESOURCES	P1	E1	S1	RES-1	RES-2	RES-3	QTY-P1	QTY-E1	QTY-S1	TOTAL CHARGE
5	PROJECT MANAGERDESIGN ENGINEERSTRESS ANALYST	1	1	1	PROJECT MANAGER	DESIGN ENGINEER	STRESS ANALYST	1	1	1
6	PROJECT MANAGERSTRESS ANALYST	1		1	PROJECT MANAGER		STRESS ANALYST	1		1
5	DESIGN ENGINEER		1			DESIGN ENGINEER			1

DISCIPLINE	RATES
PROJECT MANAGER	130.00
DESIGN ENGINEER	125.00
STRESS ANALYST	125.00

Elska19

I'd probably use vlookup within a sum, maybe something like this

=SUM((QTY-P1@row*VLOOKUP(Res-1@row,discplinerates_table,2,false)),

(QTY-E1@row*VLOOKUP(Res-2@row,discplinerates_table,2,false)),

(QTY-S1@row*VLOOKUP(Res-3@row,discplinerates_table,2,false)))

You'll have to do the out of sheet reference for the table and with this is it'll multiply by the quantity of each (which you could remove if you don't want it, I just assumed from the table) and you'll get the sum.

prime_nathaniel

Is there a reason this cannot be done as single row entries per Resource (this is generally a better practice). To do what you want you'll need to use contais() against Resources and then nested if()

KempenUSA

@prime_nathaniel THX, but no, we are building a estimating tool and certain items are controlled outside of this sheet.

prime_nathaniel

@KempenUSA gotcha then you will need to do nested If statements to handle each use case of contains() for each of your resources.