Forum Discussion

burkemch's avatar
burkemch
Copper Contributor
Nov 26, 2021

unexplained error in simple non-contiguous sum function

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

excel
Excel on Mac
Formulas and Functions

6 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 26, 2021

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

     

    • burkemch's avatar
      burkemch
      Copper Contributor
      Nov 26, 2021

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

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Nov 26, 2021

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

Resources