unexplained error in simple non-contiguous sum function

[burkemch]

I have a simple spreadsheet where im trying to total every other cell in a row. 

for some reason it is not picking up all the cells included in the range. I have made sure formatted as numbers, double clicked into formula to see that all cells are visually displayed, checked the formula has no errors, gaps etc, traced precedents and they show up as the correct cells.. yet it is returning a sum that is less than total should be. this is a simple excel function ive used for 20+ years adn I have no idea why its not working now... any ideas? using Excel for Mac version 16.51

[#2]

@burkemch 

=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?

[#3]

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

[#4]

@burkemch 

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

[#5]

thanks quadruple prawn. 

[#6]

@burkemch 

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.

[#7]

@burkemch 

I prefer to avoid lists of cells and would either use SUMIFS over the entire row or build each sequence as a distinct array and use SUM.

= SUMIFS(salaries, Item, "T")

= LET(
  m, SEQUENCE(1,COUNT(date)/2),
  T, INDEX(salaries, 2*m),
  SUM(T))