Excel Fox, the formula still missing one more criteria: Master!$U$1:$U$300,{"*1A*","*2A*","3A*","4A*","5A* ","6A*","7A*"}, I think once I get this criteria embeded into the formula it should work.

=SUMPRODUCT(ISNUMBER(SEARCH("W7ZPB1",Master!$B$1:$ B$300))*ISNUMBER(SEARCH("OD",Master!$K$1:$K$300))* ISNUMBER(SEARCH("9",Master!$U$1:$U$300))*ISNUMBER( SEARCH({"MG","BG","COL","LTC","MAJ","CPT","1LT","2 LT","CW4","CW3","CW2","WO1"},Master!$E$1:$E$300) ))

Thanks again!