Sum a column except current cell

Question

Is it possible to sum all values in a column EXCEPT the value in the current cell? That is, the cell that is to hold the result.

With a normal SUM(E:E) command the result is #REF! with a Circular dependency detected error.

Can I in some way leave out the current cell from such a sum command?

Answer 1

With a normal formula it is not possible. A custom function doesn't works as well (pure JavaScript). Therefore I wrote this little script to act as a work-around.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "Complete Range", functionName: "sumColumn"}];
  ss.addMenu("Sum", menu);
}  

function sumColumn() {
  var activeRange = ss.getActiveRange();
  var fontColors = activeRange.getFontColors();
  var data = activeRange.getValues(), sum = 0, indexText = 0;
  for(var i=0, iLen=data.length; i<iLen; i++) {
    if(typeof data[i][0] == "number") {
      fontColors[i][0] = "general-black";
      sum += data[i][0];
    } else {
      if(data[i][0] == "#REF!") {
        fontColors[i][0] = "red";
        indexText = i;      
      }      
    }
  }
  data[indexText][0] = sum;
  activeRange.setFontColors(fontColors).setValues(data);
}

Explained

The script will create a new menu entry called Sum when the file opens. Add either the SUM function (with complete column range to force a circular dependency) or the word #REF! into the cell you want the total sum to appear in and select a column range or the range that needs to be summed up:

Select from the menu the (only) option Complete Range. From this part on, the script is quite straightforward. It will sum the numbers and will store the index when it hits the #REF!. The rest will be ignored. After that, the text entry will be replaced by the total sum and the lot (data) is added to the active range.

Example

I've created an example file for you: Sum with Circular Dependency

Remarks

In order to determine the sum, I've colored the total sum in red. If you want to do it all over again, just type somewhere (once) the word #REF!.

Add the script by selecting Tools>Script editor. Save/initiate the script by pressing the "bug" button. This will trigger the authentication process of the script, because it needs to gain access to the Spreadsheet.

Answer 2

I know this page is quite old and likely no longer relevant to OP, but I came across this page via Google when trying to find a solution to the same problem. I found an alternate path so I figured I'd chime in with a simpler, formula-based solution in case someone else finds this page in the future. We can use the =Indirect() formula to influence our =sum() and get everything outside of the current row without circular reference errors. Using OPs original example of column E:

=(sum(indirect("E1:E"&row()-1))+sum(indirect("E"&row()+1&":E999999")))