Skip to content

Instantly share code, notes, and snippets.

@Weiyuan-Lane

Weiyuan-Lane/google_sheet_count_formula.md

Last active December 24, 2023 09:31
Show Gist options
  • Save Weiyuan-Lane/4727e6dea9626c51c4f269df822edffd to your computer and use it in GitHub Desktop.
Save Weiyuan-Lane/4727e6dea9626c51c4f269df822edffd to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Weiyuan-Lane revised this gist Dec 24, 2023. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions google_sheet_count_formula.md
    View file
    Original file line number Diff line number Diff line change
    @@ -25,6 +25,7 @@ LABEL
    #### L2
    - Perform a `SELECT` query on column A's data, with a `COUNT` column
    - `WHERE` is also used to conditionally filter away empty content and content that you don't want to count
    - `GROUP BY`, `COUNT` and `ORDER BY` can be used to emulate functionalities like sorting

    #### L11
    - `LABEL COUNT(A)''` simply omits setting a label for the output count column by using the `''` empty string
  2. Weiyuan-Lane revised this gist Dec 24, 2023. 1 changed file with 17 additions and 17 deletions.
    34 changes: 17 additions & 17 deletions google_sheet_count_formula.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,19 +1,3 @@
    ## Explanation

    #### L1
    - Using the [QUERY Function](https://support.google.com/docs/answer/3093343?hl=en) of Google Sheets
    - The column or row of cells should be in your first parameter of this function
    - `A2:A` is my column to sample the content from, starting from row 2 of column A to the last value
    - Replace `A` to the desired column value that you are referencing

    #### L2
    - Perform a `SELECT` query on column A's data, with a `COUNT` column
    - `WHERE` is also used to conditionally filter away empty content and content that you don't want to count

    #### L11
    - `LABEL COUNT(A)''` simply omits setting a label for the output count column by using the `''` empty string
    - Try omitting it to see what happens

    ```
    =QUERY(A2:A, "
    SELECT
    @@ -28,4 +12,20 @@ ORDER BY
    LABEL
    COUNT(A)''
    ")
    ```
    ```

    ## Explanation

    #### L1
    - Using the [QUERY Function](https://support.google.com/docs/answer/3093343?hl=en) of Google Sheets
    - The column or row of cells should be in your first parameter of this function
    - `A2:A` is my column to sample the content from, starting from row 2 of column A to the last value
    - Replace `A` to the desired column value that you are referencing

    #### L2
    - Perform a `SELECT` query on column A's data, with a `COUNT` column
    - `WHERE` is also used to conditionally filter away empty content and content that you don't want to count

    #### L11
    - `LABEL COUNT(A)''` simply omits setting a label for the output count column by using the `''` empty string
    - Try omitting it to see what happens
  3. Weiyuan-Lane revised this gist Dec 24, 2023. 2 changed files with 31 additions and 13 deletions.
    31 changes: 31 additions & 0 deletions google_sheet_count_formula.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,31 @@
    ## Explanation

    #### L1
    - Using the [QUERY Function](https://support.google.com/docs/answer/3093343?hl=en) of Google Sheets
    - The column or row of cells should be in your first parameter of this function
    - `A2:A` is my column to sample the content from, starting from row 2 of column A to the last value
    - Replace `A` to the desired column value that you are referencing

    #### L2
    - Perform a `SELECT` query on column A's data, with a `COUNT` column
    - `WHERE` is also used to conditionally filter away empty content and content that you don't want to count

    #### L11
    - `LABEL COUNT(A)''` simply omits setting a label for the output count column by using the `''` empty string
    - Try omitting it to see what happens

    ```
    =QUERY(A2:A, "
    SELECT
    A, COUNT(A)
    WHERE
    A IS NOT NULL AND
    A != 'Others (Custom)'
    GROUP BY
    A
    ORDER BY
    COUNT(A) DESC
    LABEL
    COUNT(A)''
    ")
    ```
    13 changes: 0 additions & 13 deletions google_sheet_count_formula.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1,13 +0,0 @@
    =QUERY('All #TechWalks'!D2:D, "
    SELECT
    D, COUNT(D)
    WHERE
    D IS NOT NULL AND
    D != 'Others (Custom)'
    GROUP BY
    D
    ORDER BY
    COUNT(D) DESC
    LABEL
    COUNT(D)''
    ")
  4. Weiyuan-Lane created this gist Dec 24, 2023.
    13 changes: 13 additions & 0 deletions google_sheet_count_formula.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,13 @@
    =QUERY('All #TechWalks'!D2:D, "
    SELECT
    D, COUNT(D)
    WHERE
    D IS NOT NULL AND
    D != 'Others (Custom)'
    GROUP BY
    D
    ORDER BY
    COUNT(D) DESC
    LABEL
    COUNT(D)''
    ")