I need to search column B for cells that meet a range of data on a seperate sheet. Looking at the below spreadsheet, I want to find out how many officers, based on the rank entered in first part of Column B, are Males (COL C) for each unit (HHB, BRAVO, CHARLIE, CPN, NATO, ENG, AIR FORCE). The ranks are located on the second sheet in O9:O29. For instance if the cell contains a Rank found in cells C9:18 (which are officers) and they are M, MALE (COL C), then I want to count that cell, if not skip it. I need to do this for three seperate rank groups:
OFFICERS O9-O18
NCO O19-O25
SOLDIER O26-O29

Then I need to do the same calculation except count the cells only if COL B was F, FEMALE. I have two more calculations needed still then, same search (officers, NCO, Soldier/male or female/) but now I have to also check COL D for which shift they are on (D/N).

All these calculations will go on a report that will report the following:
X UNIT
DAY SHIFT NIGHT SHIFT Barracks(+) On-Site(*)
Male Officers Female Officers Male Officers Female Officers Male Officers Female Officers Male Officer Female Officer
Male NCO Female NCO Male NCO Female NCO Male NCO Female NCO Male NCO Female NCO
Male Soldiers Femal Soldiers Male Soldier Female Soldiers Male Soldier Female Soldier Male Soldier Female Soldier

The other part of this report will then again brekadown based on rank range and gender, then see if they are staying in Barracks or On-Site. The difference between the two is shown with a + after their name or a *. This is alot to digest and to figure out without an extra pair of eyes or some expert advice. I have attached the spreadsheet below and politely ask that you not change or modify data, design or pre-existing formulas.

Thanks in advance for any help given!!

Site-G Roster.xlsx