4

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.

Example

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

2 Answers 2

5

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:

enter image description here

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.

1
  • Impressive work, and thanks a lot. But what a solution for a simple wish.
    – Steeven
    Oct 5, 2013 at 21:47
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.