Can anyone suggest a smarter way of writing this SUMPRODUCT formula that has AND / OR within it.
My intention was to have a formula such as:
=SUMPRODUCT(($C$2:$C$126=$O131)*($I$2:$I$126=P$130)*($AF$2:$AF$126="Missing")*$H$2:$H$126)-SUMPRODUCT(($C$2:$C$126=$O131)*($I$2:$I$126=P$130)*($AG$2:$AG$126="Resigned", "Leaver", "Transfer")*$H$2:$H$126)
I am trying to show Resigned, Leaver and Transfer combined.
=SUMPRODUCT(($C$2:$C$126=$O131)*($I$2:$I$126=P$130)*($AF$2:$AF$126="Missing")*$H$2:$H$126)-SUMPRODUCT(($C$2:$C$126=$O131)*($I$2:$I$126=P$130)*($AG$2:$AG$126="Resigned")*$H$2:$H$126)+-SUMPRODUCT(($C$2:$C$126=$O131)*($I$2:$I$126=P$130)*($AG$2:$AG$126="Leaver")*$H$2:$H$126)+-SUMPRODUCT(($C$2:$C$126=$O131)*($I$2:$I$126=P$130)*($AG$2:$AG$126="Transfer")*$H$2:$H$126)
If there is not, please let me know. I am sure with so many smart individuals on this forum, one could suggest an alternative.
Thanks
Bookmarks