Google Sheets – How to Count Occurrences of ‘STRING’ in a Row Starting with ‘IFTEXT’

formulasgoogle sheets

I have a sheet with employee time logs, as follows:

Name    Day1       Day2       Subtotal of 'OFF' days this period
Jess    PRESENT    OFF        1
Bob     PRESENT    PRESENT    0
Name    Day3       Day4       Subtotal of 'OFF' days this period
Jess    PRESENT    PRESENT    0
Bob     OFF        OFF        2

I need to create an automatic total of the number of days off each employee has had. How could I do this? =COUNTIF(1:100,"OFF") would return the total number of off days, but is there a way to get this total only rows where the first cell contains "Jess"?

(In this example, "OFF" is "STRING" in my question, while "Jess" is "IFTEXT")

Best Answer

If you don't want to precalculate the subtotal of off days, you can use an array formula:

=arrayformula(SUM((A:A="Jess")*(B:C="OFF")*1))

A:A="Jess" returns a bunch of TRUE and FALSE, in your sample, it would be {TRUE, FALSE, FALSE, TRUE, FALSE}

B:C="OFF" returns another bunch of TRUE and FALSE, in your sample, it would be {FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE} (note semicolons denoting the different rows)

And those two multiplied is evaluated as:

From A:A="Jess"  From B:C="OFF"   Result
TRUE             (FALSE, TRUE ) = FALSE TRUE
FALSE            (FALSE, FALSE) = FALSE FALSE
FALSE            (FALSE, FALSE) = FALSE FALSE
TRUE             (FALSE, FALSE) = FALSE FALSE
FALSE            (TRUE , TRUE ) = FALSE FALSE

Only a TRUE multiplied by another TRUE gives a result of TRUE. The *1 last converts every TRUE to 1. SUM then adds all the 1 together.

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Google Sheets – Filter Based on Data Validation Dropdown

Okay so I solved this problem with this function:

=sum(filter(J6:J25,K6:K25="Meals",month(B6:B27)=7))

This would give me the subtotal for all Meal expenses for the month of July. This is a bit of a pain to apply when referencing this data in other sheets, but it works.

Best Answer

Related Solutions

Google-sheets – Fetch cell row and column indices in Google Sheets

Code

Explained

Screenshot

Example

Google Sheets – Filter Based on Data Validation Dropdown

Related Topic