# Find How Many Times Each Value Repeat in a Column in Google Sheets

0
77

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.

=countif(A2:A10,”Mango”)

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

Sample Data:

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.

SHARE

A technology enthusiast and addictive blogger who likes to travel and wish to mingle with different community of people from around the world.