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
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.
COUNT if there are notes
CountIfNote returns a 3 (which you can see above) since there are three cells with notes.
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, 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, indicating that it is a Google Script. After making these two, save them, return to your spreadsheet, and type in the formulas. It should work for you. See the video and linked sheet for further clarification.
