1

I'm trying to count the amount of cells that contain a function in them without having =ISFUNCTION(cell) next to every single cell and counting the amount of cells that return TRUE.

I tried to use =SUMPRODUCT(INT(ISFUNCTION(A3:A))), but this only returns 1 or 0. When testing this same method with =SUMPRODUCT(INT(ISTEXT(A3:A))), it returns the number of cells that contain text, similar to what I'd want isFunction to do. I'm not sure why it works with isText but not with isFunction.

1

1 Answer 1

1

The isformula() spreadsheet function does not work in an array formula, so you will have to use a script, perhaps a custom function like this:

/**
* Counts the number of spreadsheet formulas in a range.
*
* @param {"A2:A42"} rangeA1 A text string that specifies the cell range where to count formulas.
* @param {A2:A42} dynamic_reference Optional. The same range as a normal range reference, to ensure that the results update dynamically.
* @return {Number} The number of spreadsheet formulas in rangeA1.
* @customfunction
*/
function CountFormulas(rangeA1, dynamic_reference) {
  // version 1.0, written by --Hyde, 28 October 2021
  if (typeof rangeA1 !== 'string') {
    throw new Error('CountFormulas expected rangeA1 to be a text string like "A2:A42" but got ' + Object.prototype.toString.call(rangeA1) + ' instead.');
  }
  const range = SpreadsheetApp.getActiveSheet().getRange(rangeA1);
  const formulas = range.getFormulas().flat().filter(String);
  return formulas.length;
}

Use the custom function like this:

=CountFormulas("A3:A", A3:A)

1
  • Excellent adaptation.
    – Tedinoz
    Oct 29, 2021 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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