Google Sheets – How to Calculate Sum Based on Column Number from Another Cell

Replace ISEMPTY with ISBLANK

Your formula should be as follows:-

=SUM(FILTER(A:A, ISBLANK(B:B)))

Now you will get the result as 40

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.