You can use the =UNIQUE() and =COUNTIF() functions to count the number of occurrences of different values in a column in Google Sheets.
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, let’s enter the names for some professional basketball teams in column A:
Step 2: Find the Unique Values in the Column
Next, let’s use the =UNIQUE() function to create a list of every unique team name in column A:
Note that this function creates an array of unique values by default.
Step 3: Count the Occurrence of Each Unique Value
Next, let’s use the =COUNTIF() function to count the number of occurrences of each unique team name:
Note that we simply copy and pasted the formula in cell E2 to each of the remaining cells in column E.
From the output we can see:
- The team name ‘Hornets’ occurs 1 time in column A.
- The team name ‘Hawks’ occurs 3 times in column A.
- The team name ‘Spurs’ occurs 5 times in column A.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Find Outliers in Google Sheets
How to Sum Values by Category in Google Sheets
How to Perform a Median IF Function in Google Sheets
Is there a way to do this all at once for multiple values?
Hello there,
I would like to ask a question here as I am stacking badly with the task I have been assigned to …
I am very new using Google Spreadsheets and very well stacking wit the task i have been assigned to ..
I have Google spreadsheet with the data that includes Countries, number of artists from those countries and also entries.
Now I have have to get numbers of countries, people and entries from that Google spreadsheet.
Would you be able to help me out how to get those numbers …?
I am not sure if it is clear enough as I am not native speaker.
Thank you very much.
Robert
How do I apply this formula across three columns? I have a form with three questions that each contains the same list of names. Respondents select one name from each list. I want a formula that will pull together a list of unique names across the three columns then tell me the number of occurrences for each name across those same columns. Basically, it’s a ballot. I want to know which name had the most votes, second-most votes, third-most votes, etc.
Question: I have a Department, Monitor Model & Monitor Model 2 columns
How would I find the number of monitors for each department?
Thx for any help ..
Hi, how would I count all mentions of a word in a sheet, even if the word is used twice in one cell? Example
A1=”dog”
A2=”cat”
A3=”dog, dog”
I want to count all 3 mentions of “dog”
Zach
thanks for the info!
I’m trying to make a dot plot in sheets.
It requires having a column for the current frequency. Is there a function for this? ie
input: (2 3 3 4 4 4 5 6 6 6 6 7 9 9 )
output:(1 1 2 1 2 3 1 1 2 3 4 1 1 2)
regards
For some reason this doesn’t work for me – it just produces an echo of the values in the range I’m trying to count . . .
None of the counting functions seem to work.
Hi Geoff…It sounds like you’re encountering issues with using the counting functions in Google Sheets. Let’s go over some of the most common functions for counting occurrences and troubleshoot why they might not be working as expected.
### Common Counting Functions in Google Sheets
1. **COUNTIF**:
– **Syntax**: `=COUNTIF(range, criterion)`
– **Example**: `=COUNTIF(A1:A10, “apple”)`
– **Description**: Counts the number of cells within a range that meet a single criterion.
2. **COUNTIFS**:
– **Syntax**: `=COUNTIFS(range1, criterion1, [range2, criterion2, …])`
– **Example**: `=COUNTIFS(A1:A10, “apple”, B1:B10, “>5”)`
– **Description**: Counts the number of cells across multiple ranges that meet multiple criteria.
3. **COUNT**:
– **Syntax**: `=COUNT(range)`
– **Example**: `=COUNT(A1:A10)`
– **Description**: Counts the number of cells that contain numbers within a specified range.
4. **COUNTA**:
– **Syntax**: `=COUNTA(range)`
– **Example**: `=COUNTA(A1:A10)`
– **Description**: Counts the number of non-empty cells within a range.
### Troubleshooting Tips
1. **Check the Criterion Format**:
– Ensure that the criterion you’re using in functions like `COUNTIF` is formatted correctly. For example, if you’re counting text, make sure it’s enclosed in quotes: `”apple”`.
– If you’re using a cell reference as a criterion, ensure the reference is correct: `=COUNTIF(A1:A10, B1)`.
2. **Data Type Issues**:
– Ensure that the data in your range is of the expected type (e.g., text, numbers). For example, if you’re trying to count numbers, but they are stored as text (indicated by a small green triangle in the corner of the cell), the `COUNT` function won’t count them.
– You can convert text to numbers using `=VALUE(cell)` if needed.
3. **Cell Formatting**:
– Sometimes, cell formatting can interfere with counting. For example, if cells have been formatted as text but contain numbers, `COUNT` won’t recognize them as numbers. Change the formatting to “Number” to ensure proper counting.
4. **Array Formulas**:
– If you’re working with ranges and counting across them, ensure you’re not inadvertently using an array formula that might be causing unexpected behavior.
5. **Check for Hidden Characters**:
– There could be hidden spaces or non-visible characters in the cells that are affecting the count. Use `=CLEAN()` or `=TRIM()` to remove any unwanted characters.
6. **Recalculate the Spreadsheet**:
– Sometimes Google Sheets may not automatically recalculate. Force a recalculation by pressing `Ctrl + R` to refresh the sheet or by editing any cell to trigger a recalculation.
### Example: Counting Occurrences
Let’s say you have a list of items in column A (A1:A10), and you want to count how many times “apple” appears:
– **Formula**: `=COUNTIF(A1:A10, “apple”)`
– **Expected Result**: The number of times “apple” appears in the range A1:A10.
If the formula isn’t working and just echoes the values:
– Ensure you’re not copying and pasting the formula as text. Make sure the formula starts with `=`.
– Double-check that there are no errors in the range reference or criterion.
### If None of These Work
If none of the above solutions resolve the issue, you might be dealing with a more specific problem related to your dataset or a Google Sheets bug. In that case, providing more details or sharing the specific formula and dataset can help diagnose the issue more precisely.