I've read many articles on Google and StackOverflow, but haven't found any that mention how to count cells (under the same column) containing same string value. The count only considers a part of the sheet: many cells are added/removed in a short time, so the range keeps changing length. In the same sheet there are several ranges, separated by a blank row.

The counters should refer to a single range (counter_1 --> range_1; counter_2 --> range_2 , etc.).

e.g.: if cells can show 4 different options AND there are 5 dynamic ranges in the sheet --> there will be 4 counters for each range (4*5).

Following several websites (like

You can see it working here

CodePudding user response：

Here's a non-array answer. The only reason this might be helpful compared to the above two answers is if you have a lot of calculations going and you begin to hit some performance issues. The obvious drawback to the below formula is that you would have to reapply it by dragging down after changes were made. You could build in an app script to reapply the formula as an r1C1 during an onEdit event.

Put this in all cells in columns D:G and assuming D1:G1 have the matching count syntax (i.e D1=1st Option)

=if(And($A2<>"",OR(Row($A2)=2,$A1="")),SUMPRODUCT((--($A:$A=$A2))*(--(D$1=$B:$B))),)

Again the first two answers offer a dynamic solution, which is probably better, but I figured I'd add this just for illustration or maybe to ignite some other ideas.

CodePudding user response：

function countcellswithsamestring() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  const sr = 2;//data start row
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr   1, sh.getLastRow());
  const row = rg.getRow();
  const col = rg.getColumn();
  const vs = rg.getDisplayValues();
  let co = {pA:[]};
  vs.forEach((r,i) => {
    r.forEach((c,j) => {
      if(!co.hasOwnProperty(c)) {
        co[c] = {count:1,loc:[sh.getRange(row   i,col   j).getA1Notation()]}
        co.pA.push(c);
      } else {
        co[c].count  ;
        co[c].loc.push(sh.getRange(row   i,col   j).getA1Notation()) 
      }
    })
  })
  let o = co.pA.map(c => [c,co[c].count,co[c].loc.join(',')]);
  osh.clearContents();
  o.unshift(["String","Count","Locations"])
  osh.getRange(1,1, o.length,o[0].length).setValues(o);
}

Data:

Output: