Google Sheets – SUM or COUNT Values If Cells Have Notes

Overview

If you’re using Google Sheets and you have a list of amounts that you want to sum or count based on whether or not there are notes in the cells, there’s no built-in function to do it. However, there are a relatively easy set of steps to make your own functions to get it done. You’ll be able to COUNT based on cell notes and you’ll be able to SUM as well. Previously, we’ve made custom functions to COUNT or SUM by background color.

This video will walk you through the same steps described below.

Custom formulas in action

COUNT or SUM by note
Cells being counted and summed by notes

SUM if there are notes

If you look in the live spreadsheet, you will see the custom formulas being used for summing based on whether a cell has notes. This does not work for comments, only notes. The summing is done by a formula with the nice little name of SumIfNote which takes the inputs of your range, TRUE/FALSE for with/without notes, and a trigger to recalculate as explained in the associated video.

formula for SumIfNote
Formula used to sum if there are notes

COUNT if there are notes

CountIfNote returns a 3 (which you can see above) since there are three cells with notes.

CountIfNote Formula
Formula to count cells if they contain notes
Watch the video

This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you’ll like that.

Creating custom formulas

It is far easier to grab a copy of the linked sheet. How do we make these functions and any other custom function that you’re so inclined to write? First, go to Tools and you go to Script editor.. and to copy and paste code below.

 /**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function SumIfNote(sumRange, note, refresh) {
  var ss=SpreadsheetApp.getActive();
  var aSheet= ss.getActiveSheet();
  var sRange = aSheet.getRange(sumRange);
  var values = sRange.getValues();
  var sumResult=0;
  var rangeRow = sRange.getRow();
  var rangeColumn = sRange.getColumn();
  for(i=rangeRow; i<rangeRow+sRange.getNumRows(); i++) {
    for(j=rangeColumn; j<rangeColumn+sRange.getNumColumns(); j++) {
      if((aSheet.getRange(i, j, 1, 1).getNote() != "") == note) {
        sumResult += values[i-rangeRow][j-rangeColumn];
      }
    }
  }
  return sumResult;
}

function CountIfNote(sumRange, note, refresh) {
  var ss=SpreadsheetApp.getActive();
  var aSheet= ss.getActiveSheet();
  var sRange = aSheet.getRange(sumRange);
  var values = sRange.getValues();
  var countResult=0;
  var rangeRow = sRange.getRow();
  var rangeColumn = sRange.getColumn();
  for(i=rangeRow; i<rangeRow+sRange.getNumRows(); i++) {
    for(j=rangeColumn; j<rangeColumn+sRange.getNumColumns(); j++) {
      if((aSheet.getRange(i, j, 1, 1).getNote() != "") == note) {
        countResult += 1;
      }
    }
  }
  return countResult;
}

Script editor

After you go to Tools then Script editor, you come up with a blank screen. But if you don’t, just do a new script file. Paste the code into the blank window. Repeat for each code section above and name them countColoredCells and sumColoredCells. For each file, the script editor puts the “.gs” at the end of the file name which indicates that it is a Google Script. After you make these two, save them, come back to your spreadsheet, type in the formulas, and it should work for you. See the video and linked sheet for further clarification.

 Live examples in Sheets

Go to the linked sheet for examples of counting cells by notes that you can study and use anywhere you would like.