Challenging formula to calculate amount of hours b

问题:

I am working on a business travel template where I am trying to incorporate some of our policies as follows (additional hours criteria below):

In cell N15, I need the total number of hours to be added based on the criteria listed on the image below the scenario. I just need to find a formula to work out the number of additional hours. (Means of transport selection is a drop down, all available options are listed in this example.)

回答1:

I think the result you want might be achieved with:

=3*COUNTIF(K:K,"L*Outbound")+2*(COUNTIF(K:K,"S*Outbound")+COUNTIF(K:K,"L*Inbound"))+COUNTIF(K:K,"S*Inbound")+0.5*COUNTIF(K:K,"T*")  

For your example returns 9.