3 Replies Latest reply: Aug 30, 2013 9:55 PM by Barry
jjoseph6969 Level 1 Level 1 (0 points)
ABCHeader 4

Total


44



26



3

7




2


6

3



How can I add each row of cells up if one cell is empty?


MacBook Pro, OS X Mountain Lion (10.8.3)
  • Yellowbox Level 5 Level 5 (6,660 points)

    Hi JJ,

     

    An empty cell is treated as zero. Text is ignored.

     

    Screen Shot 2013-08-31 at 11.17.34 AM.png

     

    I have place SUM in a Header Column so that the formula refers to the whole Row.

     

    =SUM(2:2) and Fill Down.

     

    Regards,

    Ian.

  • Yellowbox Level 5 Level 5 (6,660 points)

    I may be wrong about empty cells being treated as zero. Probably thay are just ignored. I tried AVERAGE() and empty cells were ignored, whereas cells containing 0 were included.

     

    Screen Shot 2013-08-31 at 11.28.54 AM.png

     

    Regards,

    Ian.

  • Barry Level 7 Level 7 (29,210 points)

    Hi J,

     

    Ian's solution takes advantage of one of the properties of Header columns:It allows defining the 'whole row' using only the row number. The 'whole row' in this case does not include the cell in the header column, thus side-stepping the 'cell references itself' error that would rise if it were included.

     

    Since your example shows the row's total being reported in the rightmost column, you will not be able to use that shortcut; you must use both column and row labels in the definitions of the first and last cells of the range to be summed.

     

    Using your example, this is the formula you need in E2: =SUM(A2:C2)

     

    Filled down to E7, the formula will automatically adjust to refer to the sme three columns in the row containing the formula.

     

    Regards,

    Barry