Skip to content

Instantly share code, notes, and snippets.

@eschmar
Last active December 5, 2022 10:38
Show Gist options
  • Save eschmar/53f9becc409ea3951dc00cc269b7387c to your computer and use it in GitHub Desktop.
Save eschmar/53f9becc409ea3951dc00cc269b7387c to your computer and use it in GitHub Desktop.
Google sheets (Apps Script) custom function to count cells with a given background color.
/**
* Counts cell values in a range if they have the given background color
* =countByColor("V16", "S12:W20")
*
* @param {String} Cell reference for color to compare as String.
* @param {String} Range to iterate through as String.
* @return {int} Count of all cells matching background color.
*/
function countByColor(cellColorToCompare, rangeToCount) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
let color = sheet.getRange(cellColorToCompare).getBackgroundColor();
let rangeToCheck = sheet.getRange(rangeToCount);
var bgColors = rangeToCheck.getBackgrounds();
let cols = rangeToCheck.getNumColumns();
let rows = rangeToCheck.getNumRows();
var cnt = 0;
for (var i = 0; i < rows; i++) {
for (var j = 0; j < cols; j++) {
if (bgColors[i][j] == color) cnt++;
}
}
return cnt;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment