Learn how to effortlessly count unique values using the QUERY function in Google Sheets with this step-by-step tutorial.

Counting unique values enhances your understanding of data. For example, it helps identify the daily count of unique product sales.

The regular QUERY formula typically provides the count of products per day.

In this tutorial, I’ll guide you through two approaches for counting unique values in Google Sheets QUERY. One method involves a virtual helper column with a running count, while the other utilizes a UNIQUE range.

I’ll explain both methods, and you can choose the one that suits your needs.

Deciphering Sample Data

The sample comprises three columns: Date, Item, and Qty, depicting the date-wise sales quantity of Apples, Bananas, and Oranges.

On 10/11/2023, we had two sales of Apples and two of Oranges. While the actual count of sales is 4, the unique count is 2.

Now, besides counting unique values in QUERY (query 1), we also need to find the average sales quantity (query 2). How can we achieve this?

As mentioned at the beginning of this tutorial, there are two approaches, and here they are.

Count Unique in Google Sheets Using QUERY with UNIQUE

Before I dive into the formula, let me explain the logic.

Firstly, we need to obtain unique values from two columns: the column we want to group, which is the date column, and the column for counting unique items.

While we can use =UNIQUE(A2:B) to achieve this, I prefer a more flexible approach, especially when the group and unique columns may not necessarily be adjacent.

Specifying the ranges separately is the ideal method. Therefore, I will use the formula =UNIQUE(HSTACK(A2:A, B2:B)) to obtain unique values from these two columns. As you may know, the HSTACK function in Google Sheets is utilized to combine two or more ranges into a single range.

This unique data will be used in the QUERY formula for counting unique values. Here is the QUERY formula:

=QUERY(UNIQUE(HSTACK(A2:A, B2:B)), "select Col1, count(Col2) where Col1 is not null group by Col1")

This formula in cell E2 will return the result shown in “query 1” in our screenshot.

To obtain the result shown as “query 2,” first, insert the above formula in cell E8. Then, use another QUERY in cell G8 to calculate the average of the last column (Qty). In this case, the UNIQUE function is not required. Here’s the formula:

=QUERY(HSTACK(A2:A, C2:C), "select avg(Col2) where Col1 is not null group by Col1")

Anatomy of the Formulas:

Syntax of the QUERY Function:

QUERY(data, query, [headers])

In the “query 1” formula:

• data: The dataset on which the query is performed. In this case, it is the unique list of combinations of dates and items obtained by applying the UNIQUE function to the horizontally stacked range using HSTACK(A2:A, B2:B).
• query: The query string that defines the operation to be performed on the data. In the provided example, the query is "select Col1, count(Col2) where Col1 is not null group by Col1". This query selects the unique values in Col1 (dates) and counts the occurrences of each value in Col2 (items), excluding null values in Col1, and groups the results by Col1.

In the “query 2” formula:

• data: The dataset on which the query is performed. In this case, it’s the horizontally stacked range using HSTACK(A2:A, C2:C).
• query: In this case, the query string is "select avg(Col2) where Col1 is not null group by Col1". This query string will return a single column containing the average of the values in Col2 for each value in Col1.

How to Use Running Count to Count Unique Values in Google Sheets QUERY

This approach involves utilizing a helper column, which can be implemented as either a physical or virtual column. To begin, we need to set up the helper column.

Helper Column (Running Count)

To return the running count of values in a column, such as A2:A, we can use the following COUNTIFS array formula (in cell D2):

=ArrayFormula(COUNTIFS(A2:A, A2:A, ROW(A2:A), "<="&ROW(A2:A)))

However, to find the running count of the date and item columns, we need to combine the two columns into a single column. Here is the running count formula for this:

=ArrayFormula(COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), "<="&ROW(A2:A)))

This formula will return 1 for the first occurrence of each unique date and item combination, 2 for the second occurrence, and so on.

We can then replace any value greater than 1 with 0 to get the unique count of date and item combinations. Here is the formula for that:

=ArrayFormula((COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), "<="&ROW(A2:A))<2)*1)

Count Unique in QUERY

You can use the running count column (D2:D) in two ways in your count unique QUERY: as a physical column or as a virtual column. In either case, you simply need to sum the running count column to get the unique count of items by date.

Here is the “query 1” formula (cell E2):

=QUERY(A2:D, "Select Col1, sum(Col4) where Col1 is not null group by Col1")

Explanatioin:

This QUERY formula sums values in Col4 grouped by unique values in Col1, excluding null values. The result is a table showing each unique value in Col1 with the corresponding sum of values in Col4.

Here is the “query 2” formula (cell E8):

=QUERY(A2:D, "Select Col1, sum(Col4), avg(Col3) where Col1 is not null group by Col1")

Explanation:

This QUERY formula selects unique values in Col1 along with the sum of values in Col4 and the average of values in Col3. The results are grouped by unique values in Col1, excluding null values. The output is a table displaying each unique value in Col1, its corresponding sum in Col4, and the average in Col3.

If you want to use a virtual helper column instead, replace A2:D in both formulas with the following:

HSTACK(A2:D, ArrayFormula((COUNTIFS(A2:A&B2:B, A2:A&B2:B, ROW(A2:A), "<="&ROW(A2:A))<2)*1))

Conclusion

We have explored two distinct approaches to counting unique values using the QUERY function in Google Sheets.

The choice of using QUERY in this context is to produce a tabular output. Alternatively, you could opt for COUNTUNIQUEIFS and nested MAP Lambdas to achieve a similar outcome without using QUERY. However, for simplicity, I’ve demonstrated the QUERY method.

Additionally, your unique counting requirements using QUERY might vary. If you seek a tailored solution, please feel free to share your sample sheet with us in the comments below.

I hope you enjoyed this discussion of count unique in Google Sheets QUERY. Please let me know if you have any questions or comments.