Google Sheets Tips

Filter not empty:

• =FILTER(A:B, B:B <> "")

Count number of non-blank values:

• =COUNTA(B:B)

Count a String value:

• =COUNTIF(A:A;"value")

Nested Count If

• =COUNTIFS(B:B, "Green", C:C, "No")

Count String contains value

• =COUNTIF(A:A, "*TEST-*")

Escape double quotes in a link using Ruby

escaped = link.gsub('"', '""')

hyperlink("some""link""", "text")

Filter from another sheet:

• =filter(Sheet1!A:B, isnumber(find("someText", Sheet1!B1:B)))

---

List rows not equal to "x"

• =FILTER(A:H, H:H <> "x")

A, B, C, D, E, F, G, H
0, 0, 0, 0, 0, 0, 0, x
0, 0, 0, 0, 0, 0, 0, ok

---

List if the website is included.

• =ARRAYFORMULA(IF( ISNUMBER(FIND("example.com", A2:A)), "Yes", "No"))

Now Filter Views can be used on that boolean column to only display rows that match.

A	B
URL	Internal Domain?
example.com	=ARRAYFORMULA(IF( ISNUMBER(FIND("example.com", A2:A)), "Yes", "No"))
otherwebsite

---

Filter in one list and not another.

• =filter('sheet_1'!A:A,isna(match('sheet_1'!A:A,'sheet_2'!A:A,0)))

--

Filter out one column based on the value of another.

A	B
TRUE	A
FALSE	B

• =filter(B1:B, A1:A=TRUE)

Shows all values of B where column A is TRUE.

---

Counting dates

Easy way to fix is to select the dates, Format → Number → Plain text

A	B
Date	Text
Thu Dec 3, 2015	Thu Dec 3, 2015

• =COUNTIF(A:A, "*Thu*") returns 0 because text matching doesn't work on dates
• Create a new column with the date as text
  • =ARRAYFORMULA(IF(ISBLANK(A:A), "", TEXT(A:A, "ddd mmm d, yyy")))
• Now =COUNTIF(B:B, "*Thu*") returns 1.