Countif(s): Need to properly count a flight plan

Hello all,
This is not a normal flight plan spreadsheet. What I am doing is I have a flight plan: Plane going from "A" to "B" to "C" to "D" to "A", and a lot of other combinations of flights. I need to count the number of times that there are flights "A-B", "B-A", "C-D", and "D-C" while disregarding all other stops ("E", "F", "G", etc).
I have used this: =COUNTIF(D9:R9,{"A","B"}) with a little success, but is is counting for the extra "A" as well as shown in the example flight plan above.

Is there a way to create a criteria that specifically states that there has to be both in the row to count as one and leave off the excess "A"?

Thanks folks, you guys to great work here.

Or maybe if there was a way to only count if there were the same amount of "A" stops as "B" stops? Anyone?

Air Movement Tracker.xlsx

W4:Y12 is the area of concern. Also the numbers the formula should populate.
W4:Y4 is what I need the formula for.
The three combos I need are MSF-NRK, NRK-MSF; MSF-SBZ, SBZ-MSF; and NRK-SBZ, SBZ-NRK

Hope this helps, This is the first time I've needed help on a spreadsheet. Thanks in advance!

Hi try this
=COUNTIFS(A:A,"A",B:B,"B")

Punnam

Yes the results from that countif formula are incorrect and the values in W4:Y12 are what I need to come out via the formula.

Originally Posted by Punnam

Hi try this
=COUNTIFS(A:A,"A",B:B,"B")

Punnam

The problem with using this is that A:A might not always be "A".
Sorry for the tricky problem.

Hi,

I have changed the layout . check it once.


Punnam

Originally Posted by XOR LX

So there can only ever be a maximum of 3 entries in a given row for the 2 desired flight codes?

So if there are 2 (i.e. one of each - a "single" journey) then the result is 1; if there are 3 (i.e. one of one and two of the other - a "return" journey) then the result is 2?

Or could it go back and forth multiple times in a single row?

And your result for row 5 is not in error, i.e. it doesn't matter if there are other destinations in between (NRK to MSF to PSR to NRK)?

Regards

Not exactly sure what you mean here. The dropdown list is a list of airports and they can fly in any manner to wherever. I need to track every time there is a flight either from MSF to NRK, NRK to MSF (in W:W); SBZ to MSF, MSF to SBZ (in X:X); and SBZ to NRK, NRK to SBZ (in Y:Y) these flights can have stops in between to places that I do not track. Maybe a better way to explain this is I am tracking the relationship between the three airports. Not in error. Thanks guys!

@XOR LX
Sorry, I do get a little ahead of myself. Yes the results can be more than just one. The first example needs to produce a value of 4.
Thanks for hanging in there with me!

The first example you had on post #14, thanks

Absolutely! I have no problem with that!

Internet Excel BEAST, lol I humbly explain...
It is late over here and I am tired. Yes, they should be what you showed. My apologies, keep rocking the brain cells.

So would this be a countif formula or am I looking at something else?

Anybody? Somebody?

Well I have come up with a partial solution. I broke down the flight plan as was able to create a separate chart on another sheet and then use countifs to handle the criteria; Ex: msf-sbz-nrk, to: msf-sbz; sbz-nrk etc. That works for everything EXCEPT when there is a flight needing to be tracked that does not go directly to one of the airports being recorded. Ex: msf-fsl-sbz. This example still needs to count as 1. Any Ideas? @X0R LX

I don't know how people like you do it. I am so very impressed. Thank you very much. I will consider this solved, but I do have one more caveat. My boss just said earlier today that he wants to track msf-nrk seperately from nrk-msf. Is this possible? Like I said, you have been more help to me than I deserve and it's OK if you can't!

Thanks again!

Air Movement Tracker.xlsx
OK, this is the updated book.
My expected results in W13 and X13 would be 1 and 1. That way it shows that there was one leg (nrk-msf) and one leg (msf-nrk)

Also please check the arithmetic on rows 4,5,and 6. In Row 4, it seems to be double counting a stop.
Thanks again!

OK.
My expected result for W4:AB30 is to have each column specify how many times that the listed leg occurred in the flight in the order that is listed above that column.
Ex. for W2,3 it is listed nrk then msf below. That column should only count the number of times that the flight flew a nrk to msf leg.
for X2,3 it is listed msf then nrk below. That column should only count the number of times that the flight flew a msf to nrk leg.

Disregard the arithmetic comment. I didn't realize the formula was capable of calculating "sbz-msf-nrk" as a sbz-nrk tally in addition to sbz-msf. That's great!

The formula works the way it is needed to except for the aforementioned. Thank you very much!

Oops. Double post.

I just added columns so that it could be changed to what I asked above.

Not exactly sure what you mean about different set of calculations. The formula you gave me works, but due to the new caveat, it needs to have two similar formulas but just separate the msf-nrk and the nrk-msf. Does that help? Not very good at describing this am I? lol

I am just about stumped on this. Looks like it's going to have to be a manual entry form after all. lol