Forum Discussion

Rachel1950's avatar
Rachel1950
Copper Contributor
Nov 19, 2023

Additional criteria added to a formula now it wont work

Good Morning,

The following formula worked:

=IFERROR(IF(SMALL(IF(($E2:$E13000=E3)*NOT(ISNUMBER(SEARCH("IQVIEW-SC-B-IQ4",$D$2:$D$1300))))*(NOT(ISNUMBER(SEARCH("SENTE-400300212", $D$2:$D$13000))))*(NOT(ISNUMBER(SEARCH("GEXP" , $AD$2:$AD$13000)))),ROW($E$2:$E$13000)-1),1)=ROW()-1,SUMPRODUCT(($E$2:$E$13000=E3)*(NOT(ISNUMBER(SEARCH("IQVIEW-SC-B-IQ4",$D$2:$D$13000))))*(NOT(ISNUMBER(SEARCH("SENTE-400300212",$D$2:D$13000))))*(NOT(ISNUMBER(SEARCH("GEXP",$AD$2:$AD$13000))))), ""),"")

 

I wanted to add an additional criteria to the formula which includes if it is equal to G1LINE to also exclude this criteria.  G1LINE  is in columns AE2 to AE13000.  When I have added it brings up the error message "there's an error with this formula. Not trying to type a formula? When the first character is an Equal..." I have tried adding a bracket, comma etc but I just cant get it to work.  Can someone please advise where I am going wrong so the formula will work?

 

Many thanks

Rachel

Formulas and Functions
    • Rachel1950's avatar
      Rachel1950
      Copper Contributor
      Nov 19, 2023
      =IFERROR(IF(SMALL(IF(($E$2:$E$13000=E3)*(NOT(ISNUMBER(SEARCH("IQVIEW-SC-B-IQ4",$D$2:$D$13000))))*(NOT(ISNUMBER(SEARCH("SENTE-400300212",$D$2:$D$13000))))*(NOT(ISNUMBER(SEARCH("GEXP",$AD$2:$AD$13000)))),ROW($E$2:$E$13000)-1),1)=ROW()-1,SUMPRODUCT(($E$2:$E$13000=E3)*(NOT(ISNUMBER(SEARCH("IQVIEW-SC-B-IQ4",$D$2:$D$13000))))*(NOT(ISNUMBER(SEARCH("SENTE-400300212",$D$2:$D$13000))))*(NOT(ISNUMBER(SEARCH("GEXP",$AD$2:$AD$13000))))),""),"")
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Nov 19, 2023

        Rachel1950 

        It's hard to modify long one-line formulae. I'd recommend to apply kind of formatting, at least use multi-line.

        =IFERROR(
  IF(
    SMALL(
      IF(
          ($E$2:$E$13000 = E3) *
          NOT(ISNUMBER(SEARCH("IQVIEW-SC-B-IQ4", $D$2:$D$13000))) *
          NOT(ISNUMBER(SEARCH("SENTE-400300212", $D$2:$D$13000))) *
          NOT(ISNUMBER(SEARCH("GEXP", $AD$2:$AD$13000))) *
          NOT(ISNUMBER(SEARCH("G1LINE", $AE$2:$AE$13000))),
          ROW($E$2:$E$13000) - 1
      ),
      1
    ) = ROW() - 1,
    SUMPRODUCT(
      ($E$2:$E$13000 = E3) *
      NOT(ISNUMBER(SEARCH("IQVIEW-SC-B-IQ4", $D$2:$D$13000))) *
      NOT(ISNUMBER(SEARCH("SENTE-400300212", $D$2:$D$13000))) *
      NOT(ISNUMBER(SEARCH("GEXP", $AD$2:$AD$13000))) *
      NOT(ISNUMBER(SEARCH("G1LINE", $AE$2:$AE$13000))) ),
    ""
  ),
  ""
)

Resources