How to count unique values with the QUERY function of google sheets?

Question

I am working with a case similar to the following table:

Producto	Referencia	Cantidad
PRODUCTO 1	1001	35
PRODUCTO 1	1001	29

I am using the QUERY formula and I need the following result:

group by Producto	count Referencia	count unique Referencia	sum Cantidad
PRODUCTO 1
PRODUCTO 2
PRODUCTO 3

Does anyone know how to do it?

Aresvik · Accepted Answer · 2021-08-27 15:41:55Z

1

Depending on your locale, try:

=arrayformula(query({A:C\if(countifs(A:A&"-"&B:B;A:A&"-"&B:B;row(B:B);"<="&row(B:B))=1;1;)};"select Col1,count(Col2),sum(Col4),sum(Col3) where Col1 is not null group by Col1 label Col1 'group by Producto',sum(Col4) 'count unique Referencia' ";1))

answered Aug 27, 2021 at 15:41

Aresvik

4,6181 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ltroncoso Over a year ago

Thanks a lot! What if the quantity column did not exist and I only wanted 'group by Producto', 'count Referencia' and 'count unique Referencia' columns?

Aresvik Over a year ago

=arrayformula(query({A:C,if(countifs(A:A&"-"&B:B,A:A&"-"&B:B,row(B:B),"<="&row(B:B))=1,1,)},"select Col1,count(Col2),sum(Col4) where Col1 is not null group by Col1 label Col1 'group by Producto',sum(Col4) 'count unique Referencia' ",1))

Collectives™ on Stack Overflow

How to count unique values with the QUERY function of google sheets?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related