Attention Master Excel Slayers:

Platform: MS Excel 2000 I don't even know if this is possible, but I know if it is, y'all will be the ones to make it happen. :) I need a formula created for column J Other Comm: 1. If B ...

Formulas & Functions

Need Help

SergeiBaklan

Sep 27, 2017

When better to start from the end (for cell J2)

=IF(
   AND(LEN(
   <previous rules>
   )>1,
      ISNUMBER(SEARCH("INV",$G2))),
   "INV",
   <previous rules>
)

where <pervious rules> are

=IF($B2=112,
   "",
   IF(
      OR(
         LEFT($B2,3)="305",
         LEFT($B2,3)="308",
         LEFT($B2,3)="311",
         LEFT($B2,3)="312",
         LEFT($B2,3)="313",
         LEFT($B2,3)="350"
      ), $H2*10%,
      IF(
         OR(
            LEFT($N2,2)="30",
            LEFT($N2,2)="40"
         ),"",
         IF(
            OR($F2="CM-3000",$F2="CM-4000"),
            "",
            $H2*5%
         )
      )
   )
)

and finally

=IF(
   AND(LEN(
   IF($B2=112,
   "",
   IF(
      OR(
         LEFT($B2,3)="305",
         LEFT($B2,3)="308",
         LEFT($B2,3)="311",
         LEFT($B2,3)="312",
         LEFT($B2,3)="313",
         LEFT($B2,3)="350"
      ), $H2*10%,
      IF(
         OR(
            LEFT($N2,2)="30",
            LEFT($N2,2)="40"
         ),"",
         IF(
            OR($F2="CM-3000",$F2="CM-4000"),
            "",
            $H2*5%
         )
      )
   )
)

   )>1,
      ISNUMBER(SEARCH("INV",$G2))),
   "INV",
   IF($B2=112,
   "",
   IF(
      OR(
         LEFT($B2,3)="305",
         LEFT($B2,3)="308",
         LEFT($B2,3)="311",
         LEFT($B2,3)="312",
         LEFT($B2,3)="313",
         LEFT($B2,3)="350"
      ), $H2*10%,
      IF(
         OR(
            LEFT($N2,2)="30",
            LEFT($N2,2)="40"
         ),"",
         IF(
            OR($F2="CM-3000",$F2="CM-4000"),
            "",
            $H2*5%
         )
      )
   )
)
)

- to check if all functuions are in 2000;

- text/number fields to be used correctly

Above is only from editor, didn't check with some values in Excel

SergeiBaklan

Diamond Contributor

Nov 02, 2017

Hi Katie,

If the only change in the logic what instead of INV you check on (CUSTOM or RUSH) is within the text, when for OR condition you may use the formula as in this sample

some custom stuff		NO PBS	=IF(SUM(COUNTIF(G17,{"*CUSTOM*","*RUSH*"})),"NO PBS","---")
that's rush			NO PBS	=IF(SUM(COUNTIF(G18,{"*CUSTOM*","*RUSH*"})),"NO PBS","---")
nothing of above		---	=IF(SUM(COUNTIF(G19,{"*CUSTOM*","*RUSH*"})),"NO PBS","---")

SUM(COUNTIF()) returns TRUE (>0) if at least one of the text appears within the cell. Formula for your sheet will be like

=IF(
   AND(LEN(
      IF($B2="112","",
         IF(OR(LEFT($B2,3)={"305","308","311","312","313","350"}),
            $H2*10%,
            IF(OR(LEFT($N2,2)={"30","40"}), "",
               IF(OR($F2={"CM-3000","CM-4000"}), "", $H2*5%
               )
            )
         )
      )
      ) >0,
      SUM(COUNTIF($G2,{"*CUSTOM*","*RUSH*"}))
   ), "NO PBS",
   IF($B2="112","",
      IF(OR(LEFT($B2,3)={"305","308","311","312","313","350"}),
         $H2*10%,
         IF(OR(LEFT($N2,2)={"30","40"}), "",
            IF(OR($F2={"CM-3000","CM-4000"}),"",$H2*5%
            )
         )
      )
   )
)

Please see the Sheet2 in the attached file

Rules+1.xls53 KB