Smarter Way of Writing This Sumproduct Formula

**Excel-Access** · 07-04-2012, 04:51 AM

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

**martindwilson** · 07-04-2012, 05:48 AM

(ISNUMBER(SEARCH({"Resigned","Leaver","Transfer"},$AG$2:$AG$126)))

**Excel-Access** · 07-04-2012, 06:23 AM

Thankk you for your suggestion. I have incorporated your idea as shown below. However it does not seem to be subtracting the amount from columns AG

=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)*($AG$2:$AG$126=(ISNUMBER(SEARCH({"Resigned","Leaver","Transfer"},$AG$2:$AG$126))))*$H$2:$H$126)

Would you know what I am doing wrong please?

**martindwilson** · 07-04-2012, 06:32 AM

($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)