Formula for Totaling monthly costs based on Location, Cost of Location, # of people going to location, cost per person with multiple trips per month

[Aleblanc]

Hello all,

I'm looking for a formula for cell J5 that can calculate what the cost would be for the entire month of May based on the information below. I am hoping to make a formula that won't require to be updated even if there are new trips with new months, # of people, (The location will always be those 3 locations)

 

[Aleblanc]

I should mention that the Base Rate + Multiplier is the cost per person and it is broken out by each location.

So I would need to multiply # of volunteers going to the specified location (R column) by the cost of that specified location (J20:L20) for each trip listed in May (O column), then have that number placed in J5 (would like the formula to automatically search O35:R35 to pick the correct month).

 

[Aleblanc]

I should mention that the Base Rate + Multiplier is the cost per person and it is broken out by each location.

So I would need to multiply # of volunteers going to the specified location (R column) by the cost of that specified location (J20:L20) for each trip listed in May (O column), then have that number placed in J5 (would like the formula to automatically search O35:R35 to pick the correct month).

***EDIT: That last sentence should say (would like the formula to automatically search O19:R35 to pick the correct month).