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 | | |
This question is related to
google-sheets
google-sheets-formula
array-formulas
The answer is
=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
=COUNTIF(A:A;"lisa")
You can replace the criteria with cell references from Column B
Put the following in B3 (credit to @Alexander-Ivanov for the countif condition):
={UNIQUE(A3:A),ARRAYFORMULA(COUNTIF(UNIQUE(A3:A),"=" & 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,"=" & B3:B))
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)
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)
Source: Stackoverflow.com