Hello, trying to calculate the number of flights we need based on what employee name is entered in the previous column.

1.png
  • In the first column, 3 flights are needed to get Jim/Mike/Steve to a course. No outbound flights are needed because looking forward, they are needed in the second column course as well
  • In the second column, 1 inbound flight is needed to get Eric to the course (the other 3 instructors are already there). Looking forward, 4 outbound flights are needed to fly all 4 instructors home since they won't be participating in the third columns course.
  • In the third column, 2 new inbound flights are needed, and 2 new outbound flights are needed (because there is nothing to the right of that column, so it can be assumed that they have to have outbound flights to leave).

My question is how do I make a formula to check each column and calculate how many inbound or outbound flights are needed based on instructor names?