Counting number of occurrences in column

Question

What would be a good approach to calculate the number of occurrences in a spreadsheet column  Can this be done with a single array formula   Example  column A is input  columns B and C are to be auto-generated        A       B       C      ------- ------- -------    Name    Name    Count    ------- ------- -------    Joe     Joe         2     Lisa    Lisa        3     Jenny   Jenny       2     Lisa                      Lisa                      Joe                       Jenny

User · Answer

arrayformula if isblank B2 B  iferror 1 0  mmult sign B2 B TRANSPOSE A2 A   A2 A      I got this from a good tutorial - can t remember the title - probably about using MMult

User · Answer

Put the following in B3  credit to  Alexander-Ivanov for the countif condition      UNIQUE A3 A  ARRAYFORMULA COUNTIF UNIQUE A3 A       amp  UNIQUE A3 A       Benefits  It only requires editing 1 cell  it includes the name filtered by uniqueness  and it is concise   Downside  it runs the unique function 3x   To use the unique function only once  split it into 2 cells   B3   UNIQUE A3 A   C3   ARRAYFORMULA COUNTIF B3 B      amp  B3 B

User · Answer

A simpler approach to this  At the beginning of column B  type    UNIQUE A A    Then in column C  use   COUNTIF A A  B1    and copy them in all row column C    Edit  If that doesn t work for you  try using semicolon instead of comma    COUNTIF A A  B1

User · Answer

COUNTIF A A  lisa     You can replace the criteria with cell references from Column B

User · Answer

Just adding some extra sorting if needed   QUERY A2 A  select A  count A  where A is not null group by A order by count A  DESC label A  Name   count A   Count   -1

User · Answer

Try    ArrayFormula QUERY A A amp          select Col1  count Col2  where Col1       group by Col1 label count Col2   Count   1      22 07 2014 Some time in the last month  Sheets has started supporting more flexible concatenation of arrays  using an embedded array  So the solution may be shortened slightly to    QUERY  A A A A   select Col1  count Col2  where Col1       group by Col1 label count Col2   Count   1

[google-sheets] Counting number of occurrences in column?

Examples related to google-sheets

Examples related to google-sheets-formula

Examples related to array-formulas