I am taking you to another handy Query function tutorial. This time we can find how many times each value repeat in a column in Google Sheets using Query formula.
In a large set of data, it’s tough to find the number of times each value repeat in a Column manually. But you can easily find the multiple occurrence of a single value or keyword in a column. Google Sheets Count functions are useful in this case.
You can use a COUNTIF function as below to find how many times a single item repeat in a Column.
But this way you can’t easily count repeating different items at a time. So, as I told you above, we can use Query function to find how many times each value repeat in a column.
Formula to Find How Many Times Each Value Repeat in a Column in Google Sheets
Please open a spreadsheet and follow the below steps carefully. It’s a must to learn new features in Google Sheets.
In this example, I want to find how many times each lot of fruits received. In Column A you can see that we have multiple receipt of each items.
Here is the simple Query formula to find this.
=QUERY(A1:D10,”Select A, count (A) group by A”,1)
You can apply this formula in any blank cell, but 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 would get the result as below.
From this we can clearly find how many times each value repeat in a column. Here we can see the product Apple repeats two times, Banana 1 time etc. This way we can clearly find how many lots of each item we’ve received so far.
Point to Note: Here in Query, I’ve used COUNT not COUNTA to count repeating text. When you individually use to count a column with text, you should use COUNTA function, not COUNT.