CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscountif
Michael Benton
Michael Benton

Reputation: 39

Count cells with same string in dynamic range

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 this, this and this), I attempted to implement this check function directly from the sheet, without involving AppsScript.

E.g.: if I add this function in D2, E2, F2, G2 (watch the table below for reference):

COUNTIF(B2:B2,”1st option”) in D2 ; COUNTIF(B2:B2,”2nd option”) in E2 ; COUNTIF(B2:B2,”3rd option”) in F2 ; COUNTIF(B2:B2,”4th option”) in G2

Each counter will check its condition and update its cell value. This will be done only for cells grouped under "1st department".

The problem is that I have to add 16 counters manually (4 options for 4 departments) and, if an item is added/removed, all counters will throw an error. I can't divide departments in different sheets as a workaround.

My sheet is as follows:

Department Option 1st option counter 2nd option counter 3rd option counter 4th option counter
1st "2nd option" 0 1 0 0
2nd "1st option" 1 1 0 0
2nd "2nd option"
3rd "4th option" 0 1 0 1
3rd "2nd option"
4th "3rd option" 0 2 1 0
4th "2nd option"
4th "2nd option"

After some items were added/removed:

Department Option 1st option counter 2nd option counter 3rd option counter 4th option counter
1st "2nd option" 0 2 0 0
1st "2nd option"
2nd "1st option" 2 1 0 0
2nd "2nd option"
2nd "1st option"
3rd "4th option" 0 1 1 1
3rd "2nd option"
3rd "3rd option"
4th "3rd option" 0 0 2 0
4th "3rd option"

Any help would be appreciated.

Upvotes: 1

Views: 279

Answers (3)

pgSystemTester
pgSystemTester

Reputation: 9917

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.

Upvotes: 2

Cooper
Cooper

Reputation: 64072

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:

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
6 10 0 5 1 2 4 5 2 3
5 7 1 5 8 0 9 8 3 8
5 1 5 5 0 4 8 6 0 3
7 4 0 6 3 8 9 8 3 5
4 7 5 1 7 9 4 6 3 9
0 0 0 7 4 7 9 2 6 1
4 2 10 10 4 4 6 6 6 9
7 0 10 0 2 10 8 0 8 1
0 0 0 0 6 9 1 4 7 8
8 9 5 3 5 8 1 4 1 6
9 5 6 7 1 4 2 5 8 7

Output:

String Count Locations
6 11 A2,H4,D5,H6,I7,G8,H8,I8,E10,J11,C12
10 5 B2,C8,D8,C9,F9
0 15 C2,F3,E4,I4,C5,A7,B7,C7,B9,D9,H9,A10,B10,C10,D10
5 13 D2,H2,A3,D3,A4,C4,D4,J5,C6,C11,E11,B12,H12
1 10 E2,C3,B4,D6,J7,J9,G10,G11,I11,E12
2 6 F2,I2,H7,B8,E9,G12
4 12 G2,F4,B5,A6,G6,E7,A8,E8,F8,H10,H11,F12
3 7 J2,I3,J4,E5,I5,I6,D11
22 K2,L2,K3,L3,K4,L4,K5,L5,K6,L6,K7,L7,K8,L8,K9,L9,K10,L10,K11,L11,K12,L12
7 10 B3,A5,B6,E6,D7,F7,A9,I10,D12,J12
8 12 E3,H3,J3,G4,F5,H5,G9,I9,J10,A11,F11,I12
9 9 G3,G5,F6,J6,G7,J8,F10,B11,A12

Upvotes: 0

Mart&#237;n
Mart&#237;n

Reputation: 10187

You can try with this formula in D2:

=MAKEARRAY(ROWS(A2:A);4;LAMBDA(r;c;IF(AND(INDEX(A2:A;r)<>"";INDEX(A1:A;r)<>INDEX(A2:A;r));COUNTIFS(A2:A;INDEX(A2:A;r);B2:B;INDEX(D1:1;c));"")))

enter image description here

You can see it working here

Upvotes: 1

Related Questions