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
How to perform counts across a row or column in Google Sheet
Raw
google_sheet_count_formula.md 
=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)''
")

Explanation

L1

  • Using the QUERY Function 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
  • 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
  • Try omitting it to see what happens
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment