Excel Screen Capture.JPG
Please can someone help me. I have tried looking this up using Excel Help websites, but can only find a formula that compares a value in one cell with values from a list/range, but I have values in multiple cells that I want to cross reference/compare against a list/range.
I want to be able to count how many pilots have worked on any given day.
The names of the pilots who have 'actually' worked are recorded in columns B, D, F and H (please see attached example screenshot)
A list of all Pilot names are recorded on another sheet e.g. Crew!A2:A11
On a typical day, 4 pilots are rostered and 4 pilots actually work, but on some days, it is necessary for a pilot to do a double shift resulting in only 3 individuals working that particular day.
I would like a formula that cross references the names of pilots who actually worked (on any given day) with the list of all pilot names and if there is a match, return a value of 1 and then sum up how many matches there are e.g. if a pilot takes another pilots place (double shift), there will only be 3 names (not 4) for that day.
I Hope this makes sense.
Thank you.
Gaynor
Bookmarks