Sometimes you may want to count the number of times a particular text or value has appeared in a column.
2 methods of counting the number of occurrences in a column in Google Sheets:
- Using the UNIQUE and COUNTIF functions
- Using a query formula
Method 1: Using the UNIQUE and COUNTIF functions
Step 1: Enter the formula =UNIQUE(range).
Enter the formula =UNIQUE(range) in a cell you want to see the results. Here, all the distinct names will show up.
Alternatively, enter the formula =UNIQUE and select the cell range by dragging your cursor across the cells.
In this example, the formula entered was =UNIQUE(A3:A15). This is simply because the data range here is A3 to A15. Also, the names showed up in column C because I entered the formula in C2.
Step 2: Enter the formula =COUNTIF(range, cell).
Select the cell you want the number of occurrences of the distinct values to appear. Enter the formula =COUNTIF(range, cell). Here the range will be the same as previous and the cell will be the unique value that it’s going to count.
Once the count shows up, drag the fill handle down to the all the values. This will show the number of occurrences of all the values in a column.
The selected range here is A3:A15. The cell in the formula is C2 because the data in the Counts column will be in relation to the respective names. And drag the fill handle to D8 to fill the remaining cells with the respective formula.
Method 2: Using the formula
Step 1: Apply the formula.
Enter =ArrayFormula(QUERY(A1:A16&{“”,””},”select Col1, count(Col2) where Col1 != ” group by Col1 label count(Col2) ‘Count'”,1)). This will directly find the unique values in a column and count its occurrence. (Source)
The formula is lengthy but you can copy-paste it. Make the necessary changes to the formula as per the spreadsheet data you want to count.
Conclusion
Counting the occurrence in a column in Google Sheets is simple once you know how to do it. You can use any of the above methods to count the occurences in a column easily.