($AG$2:$AG$126=(ISNUMBER(SEARCH({"Resigned","Leaver","Transfer"},$AG$2:$AG$126))
is just
(ISNUMBER(SEARCH({"Resigned","Leaver","Transfer"},$AG$2:$AG$126))) you dont use the first bit
=SUMPRODUCT(($C$2:$C$126=$O134)*($I$2:$I$126=AC$130)*($AF$2:$AF$126="Missing")*$H$2:$H$126)-SUMPRODUCT(($C$2:$C$126=$O134)*($I$2:$I$126=AC$130)*(ISNUMBER(SEARCH({"Resigned","Lea ver","Transfer"},$AG$2:$AG$126)))*$H$2:$H$126)
Bookmarks