Help - Formula works in one sheet but not another?

[lauraIBT]

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.

[#2]

This is an array formula, which must be entered using control+shift+Enter rather than just Enter.

[#3]

@lauraIBT 

As @Jan Karel Pieterse 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)

[#4]

@Jan Karel Pietersethank 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.

[#5]

@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 

[#6]

@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))

[#7]

@Sergei Baklan 

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

[#8]

@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.

[#9]

They return the value of F5 (ie -300) in all sheets.

[#10]

Can you please post a sample workbook which contains just one sheet that gives the zeroes? Please make sure you remove snesitive information first.