SELECT DISTINCT sum in google sheet

Question

i've a sheet like this:

Month(Col11)	Team (Col2)
03	luna
03	luna
04	pippo
04	gigi
04	luna
04	gigi
04	pippo
04	luna
04	luna
04	pippo
04	pippo
04	grisbi
04	grisbi
05	luna
05	luna
05	pippo
05	pippo
05	grisbi
05	grisbi

i need the sum of unique of each month, a result like this:

Month(Col11)	Sum of unique (Col2)
03	1
04	4
05	3

i try with: =QUERY(database_tornei!A:K;"select Col11,count(Col2) group by Col11") But i've the sum of all Teams in Col2. Don't know how to use dinstinct in query :(

Answer 1

I asked some questions in the comments below the original post, but haven't heard back yet. So I'll just provide two versions of a formula, one for each case.

If your data in Col11 is text/strings, use this:

=ArrayFormula(QUERY(UNIQUE({K2:K,B2:B}),"Select Col1, COUNT(Col1) WHERE Col1 Is Not Null GROUP BY Col1 LABEL Col1 'Month', COUNT(Col1) 'Count Unique'"))

If your data in Col11 is real numbers, use this:

=ArrayFormula(QUERY(UNIQUE({TEXT(K2:K,"00"),B2:B}),"Select Col1, COUNT(Col1) WHERE Col1 <> '00' GROUP BY Col1 LABEL Col1 'Month', COUNT(Col1) 'Count Unique'"))

In this second case, you need to convert the numbers to text in order to produce a format with a leading zero as shown in your post example.

This still does not account for distinguishing the months from different years. If your sheet will only ever have months from a single year, you don't need to worry about it. But if you will keep cumulative data that spans more than one calendar year, you will want to change the format of Col11 to something like this:

03 [2021]

04 [2021]

...

12 [2021]

01 [2022]

02 [2022]

03 [2022]

If you decide to do it this way, you can still use the first formula I provided above. It will just assure that months from different years stay separate.

Answer 2

You can wrap your existing query() in another query() like this:

=query( query(A1:K, "select K, A, count(K) where K is not null group by K, A", 1), "select Col1, count(Col3) group by Col1", 1 )

This will get the count of uniques per month.