Impressed with how little I know, after hours trying to figure it out, I have to resort to you.

I'm thinking the process should be the following: Define keywords in an array; Iterate over the range; As the keyword is found, it starts counting until next ketword is found; Counter becomes the number of rows for that category; It starts over again with the next keyword found.

I started writing it, but I can't see multiple for loops here and I wonder how else it'd be:

function formatCells() {
  //Utilities.sleep(3000);

  //Formats Categories and cell colors
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const boqPipeworkSheet = ss.getSheetByName('BOQ Pipework');
  const boqPipeworkRng = boqPipeworkSheet.getRange(5, 1, boqPipeworkSheet.getLastRow(), 14);
  const boqPipeworkValues = boqPipeworkRng.getValues();
  let categoryToPaste = [];
  let categories = ['Copper pipework'];
  let counter = 0;
  for (let r = 0; r < boqPipeworkRng.length; r++) {
    let category = boqPipeworkRng[r][0];
    if (categories.indexOf(category) > -1) {
      categoryToPaste.push(category);
      counter++
    }
  }
}

Here's how the data is:

Here is a link to a file containing an example: https://docs.google.com/spreadsheets/d/138TD3eJU6GQtRj3uxXBJicn7pZyeXvAqU8lNcU01BDw/edit?usp=sharing

Thank you!