Author: Huanfa Chen
Last update: 2023/05/30
The Google sheet used for code demonstration: here
-
To exclude a value in Cell A3 from a list
=filter({"aa","bb","cc","dd","ee"},{"aa","bb","cc","dd","ee"}<>A3)
-
To randomly pick up a value from a list that excludes a value in A3. Note that the random selection depends on all cells in this speadsheet, meaning that if you change any cell in the spreadsheet, the random value will change.
=INDEX(filter({"aa","bb","cc","dd","ee"},{"aa","bb","cc","dd","ee"}<>A3)), RANDBETWEEN(1,COUNTA({"aa","bb","cc","dd","ee"})-1)))
-
To do something conditioning on whether a cell A3 is blank
=if(isblank(A3), sth, sth_else) -
To count the number of cells in a range (Column A) that equals to a value ("")
=count(A:A,"") -
To count the number of cells that meets a criterion (greater than 5)
=countif(A:A,">5") -
To count the rows where the Column A value equals Column B (Note that you should use countifs rather than countif)
=COUNTIFS(A3:A33,B3:B33) or =COUNTIFS(A:A,B:B) -
To count the number of cells that meets multiple criteria from multiple columns. Note that countifs doesn't work with isnumber or isna
=countifs(A:A,">5",B:B,"") -
To group by a column and get the sum of the values in another column (using SQL syntax)
=QUERY(A2:C8,"select A, sum(D) group by A") -
To get the number of occurrences of a value in a column (using SQL syntax)
=QUERY(W:W,"select W, count(W) group by W")