Forum Discussion
unexplained error in simple non-contiguous sum function
=SUMPRODUCT((MOD(COLUMN(A1:I1),2)=0)*A6:I6)
=SUMPRODUCT((MOD(COLUMN(A1:I1),2)<>0)*A6:I6)
Do you work with formulas like the above ones?
no.. just very simple sum function with commas separating cells...
=SUM(D5,F5,H5,J5,L5,N5,P5,R5<T5,V5,X5,Z5<AB5<AD5<AF5,AH5<AJ5,AL5,AN5,AP5,AR5<AT5,AX5,AZ5,BB5,BD5+AV5) I should say that this shows up as irregular when I copy and paste... as it appears to change the comma to inequalities ie < on some cells, but when I click into the formula the long version has every cell that should be included, included with a "," not a "<"
I dont know why it appears to summaries some of the cells references to these inequalities for instance "R5<T5" instead of listing every cell but I assume this was just to save length... ive tried redoing it through manually typing the appropriate cell references in and by entering them by hitting "control" and clicking on each cell individually.. but it always seems to abbreviate it as shown above...
I entered the formula in my spreadsheet (removed all the < signs) and copied and pasted it and it seems to work.
Can you attach the file you work with? Then i can see the long version of your formula in comparison to the formula with the inequal signs. I can't imagine "R5<T5" to be an abbreviation. As you want to sum every other cell the original formula string should be "R5,T5".
thanks quadruple prawn.
I can't imagine why commas were replaced by < recently in some parts of your formula. I corrected formulas in AY4 and AZ4 and filled them down. Now all formulas in range AY4:AZ9 are for the same range. Before this the formula in AY4 was for a different range than formulas in cells AY5:AY9 (Formula in cell AY4 started in column C the other formulas started with column G).
After i corrected formulas in AY4 and AZ4 i copied and pasted them in other cells and everything worked as intended. I hope your latest experience is only an inexplicable exception and everything will work for you in the future like it did for more than 20+ years.