Forum Discussion

lauraIBT's avatar
lauraIBT
Copper Contributor
Oct 23, 2019

Help - Formula works in one sheet but not another?

I have a formula which works great in one sheet but not in any other sheets within the same workbook. I have tried both copy and pasting into a new sheet and retyping the formula. The formula is   ...
excel
Formulas and Functions
Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Oct 23, 2019

lauraIBT 

As JKPieterse mentioned, it is an Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone. That's because SUM function cannot handle arrays.

But you can use SUMPRODUCT which can handle arrays and you can confirm it with Enter only.

 

=SUMPRODUCT((MOD(COLUMN(F5:CM5),3)=0)*F5:CM5)

lauraIBT's avatar
lauraIBT
Copper Contributor
Oct 27, 2019

Subodh_Tiwari_sktneer

Thank you for your reply but this hasn't made a difference, your formula works perfectly in the first sheet but will only return 0 in all other sheets in the workbook. 

 

I think this might be a problem with the sheet setting rather than the formulas 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 28, 2019

    lauraIBT 

    You may try

    =SUM(IF(MOD(COLUMN(F5:CM5),3)=0,--F5:CM5,0))

    or better

    =SUMPRODUCT(IF(MOD(COLUMN(F5:CM5),3)=0,--F5:CM5,0))
    • lauraIBT's avatar
      lauraIBT
      Copper Contributor
      Oct 28, 2019

      SergeiBaklan 

      Thank you but still no change, returns 0 in all other sheets but the first.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 28, 2019

        lauraIBT , if you simply enter =SUM(F5) in any empty cell of the first sheet, and the same into the empty cell of the second sheet, what do they return? Assuming F5 is not empty in both sheets.

Resources