Count Unique values on an array based on multile criteria google sheets

Question

I have a spreadsheet where in TAB A I have a database with the "raw" data imported from another db, where I have some products with unique ID's and a few other columns.

What I'm trying to do is on TAB B, using an arrayformula, is count all of the unique numbers for the specific ID, I left an example on TAB B on the top rows using QUERY formula.

here is the spreadsheet link if you wanna take a look at the data: https://docs.google.com/spreadsheets/d/1-pFeQWVD_0fpBdC-GzHBlnr92bGDQn-6kqEdWfTAkPI/edit#gid=487890997

Thanks in advance for the help!!

Martín · Accepted Answer · 2023-01-03 13:36:20Z

1

You can use this QUERY for selecting the UNIQUE values of ID and Product number in B1:

=QUERY(QUERY('TAB A'!A:C;"SELECT A,C,COUNT(B) WHERE A is not null group by A,C");"Select Col1,Col2")

And this MAKEARRAY in D2 for the counts of unique values:

=MAKEARRAY(COUNTA(B2:B);COUNTA(D1:1);LAMBDA(r;c;COUNTA(UNIQUE(FILTER(INDEX('TAB A'!D2:F;;c);'TAB A'!A2:A=INDEX(B2:B;r))))))

answered Jan 3, 2023 at 13:36

Martín

10.4k2 gold badges7 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Count Unique values on an array based on multile criteria google sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related