Can I have a Cell Value = ARRAY FORMULA - ARRAY FORMULA

[Beth Dye]

I have two array formula's.
I want one cell to equal the subtraction of one array formula - another array formula

This is so complicated to me. I have a formula, that needs to be more than 7 arguments. To resolve, I was going to do two arrays and subtract the two

Formula 1 array
=SUM(IF(AWDS!$A$3:$A$7001="SR-REGION",IF(AWDS!$H$3:$H$7001="A",IF(AWDS!$K$3:$K$7001<>9,1,0),0),0))+(SUM(IF(AWDS!$A$3:$A$7001="SR-REGION",IF(AWDS!$H$3:$H$7001="K",IF(AWDS!$K$3:$K$7001<>9,IF(AWDS!$J$3:$J$7001<>"BP",1,0),0),0),0),0)

I need to add the argument IF(MID(AWDS!$C$3:$C$7001,9,1)="G",1,0

Can I re-write this somehow to get around the 7 argument rule?

 

[Harlan Grove]

Formula 1 array:
=SUM(IF(AWDS!$A$3:$A$7001="SR-REGION",IF(AWDS!$H$3:$H$7001="A",
IF(AWDS!$K$3:$K$7001<>9,1,0),0),0))
+(SUM(IF(AWDS!$A$3:$A$7001="SR-REGION",IF(AWDS!$H$3:$H$7001="K",
IF(AWDS!$K$3:$K$7001<>9,IF(AWDS!$J$3:$J$7001<>"BP",1,0),0),0),0),0))

I need to add the argument IF(MID(AWDS!$C$3:$C$7001,9,1)="G",1,0)

Can I re-write this somehow to get around the 7 argument rule?

Do you mean the 7 nested function call limit?

Anyway, you don't need to use multiple IFs for this. The formula above could
be rewritten as the NONARRAY formula

=SUMPRODUCT((AWDS!$A$3:$A$7001="SR-REGION")*(AWDS!$H$3:$H$7001="A")
*(AWDS!$K$3:$K$7001<>9))
+SUMPRODUCT((AWDS!$A$3:$A$7001="SR-REGION")*(AWDS!$H$3:$H$7001="K")
*(AWDS!$K$3:$K$7001<>9)*(AWDS!$J$3:$J$7001<>"BP"))

And this could be condensed to

=SUMPRODUCT((AWDS!$A$3:$A$7001="SR-REGION")*(AWDS!$K$3:$K$7001<>9),
(AWDS!$H$3:$H$7001="A")+(AWDS!$H$3:$H$7001="K")
*(AWDS!$J$3:$J$7001<>"BP"))