I am taking you to another handy Query function tutorial. This time we can find how many times each value repeats in a column in Google Sheets using the Query formula.
In a large set of data, it’s tough to find the number of times each value repeats in a Column manually.
But you can easily find the multiple occurrences of a single value or keyword in a column. Google Sheets Count functions are a great help in this case.
To find how many times a single item repeats in a column, you can use a COUNTIF function.
E.g.:
=countif(A2:A,"Mango")But this way, you can’t easily count different repeating items at a time. For that, you may require to take the help of Unique and ArrayFormula.
We will go to that combination formula later on.
As I told you above, we can use the Query function to find how many times each value repeats in a column. We will see it first.
Formulas to Find How Many Times Each Value Repeats in a Column in Google Sheets
Sample Data:
Using Query Count Function
Please follow the below steps carefully in a new Google Sheets spreadsheet.
In this example, I want to find how many times each lot of fruits are received. In Column A you can see that we have multiple receipts for each item.
Here is the simple Query formula to find this.
=QUERY(A1:D,"Select A, count (A) where A is not null group by A",1)You can apply this formula in any blank cell, but you may make sure that there are sufficient rows and columns to the Query formula to populate the result.
When you apply the above Query formula, you will get the below result.
From this, we can find how many times each value repeats in a column. Here we can see the product Apple repeats two times, Banana one time, etc.
This way, we can find how many lots of each item we’ve received so far. Please note that, here in Query, I’ve used the COUNT function, not the COUNTA, to count repeating text.
When you count a column with text, outside Query, you should use the COUNTA function, not COUNT.
Query Alternatives to Find How Many Times Each Value Repeats in a Column
I have two quite interesting combos to replace the above Query count formula. Both the alternatives are worth trying.
The first one is using Unique and Countif.
=ArrayFormula(
{
unique(A2:A),
countif(A2:A,unique(A2:A))
}
)The Curly Braces is to create a two-column array.
- The first Unique formula here returns the unique values in column A – Column # 1.
- The second Unique formula acts as the criteria in the Countif. The Countif counts the range A2:A based on it – Column # 2.
I have used the ArrayFormula function to support multiple criteria use in Countif.
This formula may return an extra row with 0 counts at the end.
To remove that, you can replace the cell range A2:A in the formula (all three occurrences) with filter(A2:A,A2:A<>"")
The above is the first alternative formula to the Query to find how many times each value repeats in a column in Google Sheets. Here is the next one.
=sortn(
{
A2:A,
countif(A2:A,A2:A)
},
9^9,2,1,1
)Here the Countif alone returns how many times each value repeats in column A.
Using Curly Brackets, we have formed an array that contains the strings in column A and the count of them in the next column.
The Sortn returns the unique rows. I haven’t used Unique since we used a two-column array and wanted to apply the Unique only to the first column.
Here also replace A2:A with the Filter as suggested in the first alternative formula.
Is there any formula available?
If I have a column (column A) of unique values data, and a second column (column E) with some values taken from the unique values and repeating again and again, I want to count the repeating values and see how many times each value repeats in column E.
Hi Khalid,
You can use this COUNTIF formula with ARRAYFORMULA:
=ARRAYFORMULA(COUNTIF(E2:E12, A2:A5))Adjust the range references as per your original ranges in the sheet.
I hope this helps!
How would you limit your results to only display the data that actually have duplicates, then their total number of duplicates? (display line items only if the results = 2 or greater)
Hi, Eli,
Use an outer Query as follows.
=query(your_formula_here,"Select * where Col2 >1")Thank you for the great write-up!
Question: how do I preserve the original length of the dataset and add a column of # of occurrences?
Hi, Sunny,
Dataset: A2:A
If so, empty B2:B and insert the following COUNTIF Array Formula in B2.
=ArrayFormula(if(A2:A="",,countif(A2:A,A2:A)))So I need to do something like this but haven’t been able to work it out yet.
I have two date columns in my source data table (received date, complete date).
I would like to create a Query that displays the results as col 1: date, col 2: count of received, col 3: count of complete. Any suggestions? Thx
Hi, Jonny,
You can try this for the range A2:B (A – received, B – completed)
=query(A1:B,"Select A,B,count(A),count(B) where A is not null group by A,B")It returns a four-column Array. So empty D1:G and insert it in cell D1.
That works great!!!!! How do I expend it to count items in multiple columns?
Hi, Hedy T,
I can explain if you share a sample sheet in your comment reply. The link won’t be published.
Syntax has changed on Google Sheets. Use ; instead , … Here the working one:
=QUERY(A2:N1500;"Select A, count (A) WHERE I = TRUE group by A ORDER BY count (A) DESC"; 1)Hi, Aruxandei George Bogdan,
The syntax is different in EU countries and others. But you can control that using the Spreadsheets settings under the File menu.
How to Change a Non-Regional Google Sheets Formula