Oct 22 2019 08:48 PM
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.
Oct 23 2019 12:34 AM
Oct 23 2019 12:49 AM
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)
Oct 27 2019 02:31 PM
@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.
Oct 27 2019 02:40 PM
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
Oct 27 2019 06:12 PM
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))
Oct 27 2019 06:21 PM
Thank you but still no change, returns 0 in all other sheets but the first.
Oct 27 2019 08:48 PM
@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.
Oct 27 2019 08:56 PM
Oct 28 2019 01:27 AM