 • 513K Members
• 1,918 Online
• 611K Conversations

# 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

# Re: Help - Formula works in one sheet but not another?

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

# Re: Help - Formula works in one sheet but not another?

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)

# Re: Help - Formula works in one sheet but not another?

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

# Re: Help - Formula works in one sheet but not another?

@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

# Re: Help - Formula works in one sheet but not another?

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

# Re: Help - Formula works in one sheet but not another?

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

# Re: Help - Formula works in one sheet but not another?

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

# Re: Help - Formula works in one sheet but not another?

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

# Re: Help - Formula works in one sheet but not another?

Can you please post a sample workbook which contains just one sheet that gives the zeroes? Please make sure you remove snesitive information first.
Related Conversations
Need help with concatenate formula
Dave Cunningham in Excel on
2 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies