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
=SUM(IF(MOD(COLUMN(F5:CM5),3)=0,F5:CM5,0))
In the first sheet it returns the sum total of the values in every third column in the range F5 to CM5. In all other sheets it returns 0.
Any ideas? Thanks a million in advance.
9 Replies
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))
- This is an array formula, which must be entered using control+shift+Enter rather than just Enter.
JKPietersethank you for the reply, but this hasn't helped, it still returns zero and in the first sheet where it does work I only used Enter.
