Forum Discussion
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 ...
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)
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
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))Thank you but still no change, returns 0 in all other sheets but the first.
