Forum Discussion

  • Randy Birch's avatar
    Randy Birch
    Brass Contributor
    Nov 12, 2019

    Hi SergeiBaklan 

     

    That seems to work and is much more elegant … with a minor caveat : had to add one column to my sheet as MOD was failing when the first column of sales data is in AJ (the 36th column).  In your sample your formula was in an odd number column (9), so I have to either figure how to tweak MOD, or add one extra column somewhere before AJ to move its data to AK.

     

    But that also points out the flaw in this approach .. any change to the number of columns I make subsequent to implementing this code will demand an even number of columns be added.  Any odd number causes the formula to error out.  Any suggestions around this issue?

     

    =SUMPRODUCT( IF( MOD(COLUMN(AK$15:AY$202),2)=1,AK$15:AY$202,0)*IF( (MOD(COLUMN(AL$15:AZ$202),2)=0)*(AL$15:AZ$202=AL1),1,0 ))

     

    EDIT:  seems I will just have to play around with the =1 and =0 values.  See that:

    x=25
    ?x mod 2
     1
    x=26
    ?x mod 2
     0

    Flip them around in the equation and should work for being in an even column.  But the issue of adding new columns and having to ensure they don't muck up MOD is still a concern, or at least something I need to keep in mind in modifying the sheet.

     

    Randy

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 12, 2019

      Randy Birch 

      Randy, you may not to hardcode 1 or zero to compare the MOD(), but instead use MOD for the first/second column in range. For my sample it could be like

      =SUMPRODUCT( IF( MOD(COLUMN(D1:J9),2)=MOD(COLUMN(D1),2),D1:J9,0)* (MOD(COLUMN(E1:K9),2)=MOD(COLUMN(E1),2))*(E1:K9="b"))

      If you add the column before the range it shall work correctly, please see attached.

       

Resources