Google-sheets – Counting number of cells in a range that contain certain text characters

google sheetsgoogle-apps-script

My question simply: Is it possible to generate the data in A3:A5 using a formula? 

          A         B        C       D       E       F

1   PASSED/TOTAL |           EXAMS ATTEMPTED
2   -------------|-------|-------|-------|-------|-------
3   1/3          | ✔exG  | ✖exZ  | ✖exH  |       |
4   3/4          | ✖exC  | ✔exJ  | ✔exR  | ✔exN  |
5   1/1          | ✔exW  |       |       |       |

(If it's not clear, columns B through F from row 3 onward show whether someone has taken an exam AND whether they passed or failed it.)

So, back to the question… I can easily determine the total number of exams in the 3rd row with something like this in A3:

=MINUS( 5, COUNTBLANK( B3:F3 ) )

But how can I determine the number of passed exams? Is there even a way with formulas to search text content of other cells with some sort of "contains" thing — or maybe even regex searching? Something like this:

=CountCellsThatContainText( "✔", B3:F3 )

Assuming there is such a thing, then I would need to combine the two together — which I also am unsure of … maybe CONCATENATE() would work for that, e.g.:

=CONCATENATE( CountCellsThatContainText( "✔", B3:F3 ), "/", MINUS( 5, COUNTBLANK( B3:F3 ) )

So….. before I posted I did a little more digging and found QUERY() but uhhh…. I'm hoping a greater mind than mine can tell me if that's even the right direction to go in.

PS: If it would make it more do-able, we could split column A into 2 or 3 columns.

Best Answer

The following little piece of code gives you the result, without prior knowledge about the character code number.

Code

function mySplit(range, delimiter) {
  var output = [], maxCol = range[0].length;
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var colCounter=0, counter=0;
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      if(range[i][j] == "") {
        colCounter++;
      } else {
        if(range[i][j].split(delimiter).length > 1) {
          counter++;
        }
      }
    }
    output.push(counter + "/" + (maxCol - colCounter));
  }  
  return output;  
}

Screenshot

enter image description here
enter image description here

Example

I've created an example file for you: Certain Text Characters
If you copy the file, then you can access the code. Add the code into your existing sheet by going to Tools\Script editor in the main menu. Paste the code and save.

Related Topic