Forum Discussion

Helen15's avatar
Helen15
Copper Contributor
Jan 20, 2022
Solved

How do I create an formula containing IFs, OR ANDs and lists?

Can anyone simplify this formula in the attached excel. This is just an example but the lists are very long and I wanted to see if there was a better way of doing this. Many thanks in advance it is MUCH appreciated.

  • Helen15 

    The formula is OK. I'd use ISNUMBER and MATCH for the range L6:L7 too; that makes it easier to add Cost Centers if necessary.

    So:

    =ROUND(IF(AND(F$2=$D3,$E3="sz"),$C3*$J$2,IF(AND(F$2=$D3,OR(ISNUMBER(MATCH($A3,$J$7:$J$13,0)),ISNUMBER(MATCH($B3,$L$6:$L$7,0)))),$C3,0)),0)

    or, slightly shorter:

    =ROUND(IF(F$2=$D3,$C3*IF($E3="sz",$J$2,IF(OR(ISNUMBER(MATCH($A3,$J$7:$J$13,0)),ISNUMBER(MATCH($B3,$L$6:$L$7,0))),1,0)),0),0)

     

10 Replies

  • Helen15 

    Although it is probable that you are not using 365 (or Excel 2021), you did not say, so this is a single dynamic array formula

    = COUNTIFS(list,costCentre)
      *(header=type)
      * amount

     

    • Helen15's avatar
      Helen15
      Copper Contributor
      Thank you ever so much for this, i have never used a sumproduct like this before.
    • Helen15's avatar
      Helen15
      Copper Contributor
      Thank you ever so much for this, i never realised you could use countif like this.
  • Helen15 

    Like this:

     

    =IF(AND(G2=$E3,ISNUMBER(MATCH(C3,$O$3:$O$12,0))),$D3,0)

     

    You can expand the range $O$3:$O$12 as needed.

      • Helen15 

        The formula is OK. I'd use ISNUMBER and MATCH for the range L6:L7 too; that makes it easier to add Cost Centers if necessary.

        So:

        =ROUND(IF(AND(F$2=$D3,$E3="sz"),$C3*$J$2,IF(AND(F$2=$D3,OR(ISNUMBER(MATCH($A3,$J$7:$J$13,0)),ISNUMBER(MATCH($B3,$L$6:$L$7,0)))),$C3,0)),0)

        or, slightly shorter:

        =ROUND(IF(F$2=$D3,$C3*IF($E3="sz",$J$2,IF(OR(ISNUMBER(MATCH($A3,$J$7:$J$13,0)),ISNUMBER(MATCH($B3,$L$6:$L$7,0))),1,0)),0),0)

         

    • Helen15's avatar
      Helen15
      Copper Contributor
      Thank you so much for this, its much easier and I have learnt something new )

Resources