Google-sheets – COUNT UNIQUE values by group ONLY IF NOT already counted in previous group

filterformulasgoogle sheetsgoogle-sheets-arrayformula

I hope to explain clearly what I'm trying to do.

I have some data in column B, grouped as shown in column A.

I'd like to count the unique values for each group present in column A, not taking into account the unique values already counted in the previous group(s).

For instance, I'd like to:

  • count unique values in 'proyecto2' NOT COUNTING the unique values already present in 'proyecto1'.
  • count unique values in 'proyecto3' NOT COUNTING the unique values already present in 'proyecto1' and 'proyecto2'.
  • count unique values in 'proyecto4' NOT COUNTING the unique values already present in 'proyecto1', 'proyecto2' and 'proyecto3'.
  • and so on…

Below you can find a Google Sheet with the solution I found, even if I'm not very happy with it, to show easily what I mean.

https://docs.google.com/spreadsheets/d/1x8S76_6dUnHr1NtUbzNzpLTpQtqan6_ohemcrGsrpC0/edit#gid=0

Basically, in column A:B, we have the INPUT DATA. You can add data in column A and B to see how it works (my method, at the moment, only work if you add one of these groups in column A: 'proyecto1', 'proyecto2', 'proyecto3', 'proyecto4', 'proyecto5' and 'proyecto6').

In column D:E, we have the output data, basically, the unique values counted by the group.

In column G:W, the formula to process the data.

Clearly, my method is working up to 'proyecto6' since in the "processing columns" I'm taking into account formula only up to 'proyecto6'.

Everything is working but my question is: could you suggest me a more dynamic way of achieving what I'm trying to do? Or the only way is to write some code?

Best Answer

  • delete everything in range D:Z

  • paste to D2 cell:

    =UNIQUE(A2:A)

  • paste to E2 cell:

    =ARRAYFORMULA(IF(LEN(D2:D); 
 MMULT(IFERROR(LEN(G2:Z)/LEN(G2:Z); 0); TRANSPOSE(COLUMN(G2:Z2)^0)); ))

  • paste to G2 cell:

    =TRANSPOSE(UNIQUE(FILTER(B$2:B; A$2:A=D2)))

  • paste to G3 cell and drag down:

    =TRANSPOSE(UNIQUE(FILTER(FILTER(B$2:B; A$2:A=D3);
 NOT(COUNTIF(INDIRECT("G2:"&ROW()-1); FILTER(B$2:B; A$2:A=D3))))))
    0

Related Topic