Google Sheets: Filter by Date and Count Text Strings

google sheets

I'm not very experienced with Google Sheets, but I found a function here on Stack Overflow and modified it a bit to do almost everything I need:

=COUNT(FILTER('2014 Form Data'!$A:$A,'2014 Form Data'!$A:$A>=Tables!$A2 , '2014 Form Data'!$A:$A<='Tables'!$B2 ,TRIM('2014 Form Data'!$G:$G)=Tables!$D$2))

Where '2014 Form Data'!$A:$A contains a date and time-stamp, Tables!A2 contains the start of a month and Tables!B2 contains the last day of the month, and Tables!$D$2 contains the value I want to count from among all the time stamped entries for that month in the column 2014 'Form Data'!$G:$G. So basically, I get a result that says something like 5 entries from March had this particular value in this column.

It works great, I love it, but I'd like it to do one more thing…I'd like it to count instances of the text string in Tables!$D$2, even when there is other text present in the cells for '2014 Form Data'!$G:$G.

Any help on this issue will be much appreciated.

Best Answer

So, basically, you are counting the rows where (among other conditions), the G column cell is exactly the text on Tables!D2. And, instead, you want to count where the G column cell contains the text on Tables!D2. Am I right?

You can do that with the following change to your formula. Replace:

TRIM('2014 Form Data'!$G:$G)=Tables!$D$2

With:

ISNUMBER(FIND(Tables!$D$2;'2014 Form Data'!$G:$G))

Related Solutions

Google-sheets – Scan row for value, get column of that match, apply column in another formula in a different cell

The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you wanted can now be interpreted using the indirect formula!

All together, you're looking at the following formula for B3 in your example:

=COUNTIF(INDIRECT("$A$2:"&CELL("address",INDEX($1:$2,2,MATCH(TODAY(),$1:$1)))),"Yes")

edit

I temporarily forgot about filter, which makes this task much easier: =COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")

Google Sheets Script – How to Pull Email from Column E

You could use a for loop and iterate down Column E, which is column 5. Then insert the cell values into an array.

var data = s.getDataRange().getValues();
//var data = s.getRange(1,5,s.getLastRow(),1).getValues();
var emails = [];

for (var row in data) {
    var cell_value = data[row][5];
    emails.push(cell_value);
}

for (var e in emails) {
    // TODO: Perform operations on emails[]
}

Best Answer

Related Solutions

Google-sheets – Scan row for value, get column of that match, apply column in another formula in a different cell

Google Sheets Script – How to Pull Email from Column E

Related Topic