Forum Discussion
Rachel1950
Nov 19, 2023Copper Contributor
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))))*(N...
SergeiBaklan
Nov 19, 2023MVP
Rachel1950
Nov 19, 2023Copper Contributor
=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))))),""),"")
- SergeiBaklanNov 19, 2023MVP
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))) ), "" ), "" )