3

I have tried to use a COUNTUNIQUE function within ArrayFormula in cell 'A2' to get a result in the range like a column B that I've set a function cell by cell. But it returns only a single value. This is my formula in cell 'A2':

=ArrayFormula(COUNTUNIQUE(D2:D7,E2:E7,F2:F7,G2:G7,H2:H7))

Any help will be greatly appreciated!

player0
  • 124,011
  • 12
  • 67
  • 124
Nhong
  • 99
  • 3
  • 12

2 Answers2

1

This problem has interested me for a while. Basically, the solution from @player0 seems obvious to me. But it is difficult to read.

Perhaps using combined arrays and implicit endings search is more appropriate:

=INDEX(
  COUNTIF(
    UNIQUE(FLATTEN(
      {"" & ROW(C2:Z), ROW(C2:Z) & "-" & C2:Z}
    )),
    ROW(C2:Z7) & "-*"
  ) - 1
)

enter image description here

contributorpw
  • 4,739
  • 5
  • 27
  • 50
0

you can do it like this:

=ARRAYFORMULA(QUERY(UNIQUE(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(D2:K<>"", "♦"&ROW(A2:A)&"♥"&D2:K, )),,999^99)),,999^99), "♦")), "♥"))),
 "select count(Col1) where Col1 is not null group by Col1 label count(Col1)''", 0))

0

or like this:

=ARRAYFORMULA({
 COUNTUNIQUE(D2:H2);
 COUNTUNIQUE(D3:H3); 
 COUNTUNIQUE(D4:H4);
 COUNTUNIQUE(D5:H5);
 COUNTUNIQUE(D6:H6);
 COUNTUNIQUE(D7:H7)})

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks a lot for your reply. However, is there any way to set this form of formula in dynamic to last row of sheet? Thanks. – Nhong Jun 13 '19 at 13:35
  • sorry for late reply. this answer works. but it's too difficult for me to understand it because i don't realize some syntax in this set of formula. However, Thank you for your help. – Nhong Aug 05 '19 at 11:42
  • it basically assigns a row number to whatever values in range D2:K then it collapses all rows and all columns into one cell and splits it all in two columns. then it picks up unique values and count them up. – player0 Aug 05 '19 at 17:59