How to combine COUNTUNIQUE and ARRAYFORMULA in a dynamic range of two or three ... or n rows

Question

My question is very similar to this one but instead of counting unique occurrences row by row I would like to do it in a range formed by 2 rows or 3 three rows... or n rows (i.e. several rows and several columns).

Here's a Google Spreadsheet sample

Any thoughts or ideas would be appreciated, thank you!

Answer 1

try:

=ARRAYFORMULA(QUERY(SPLIT(SORTN(FLATTEN(IFNA(
 SEQUENCE(MAX(IF(A3:E<>"", 1)*ROW(B3:B))-(ROW(B3)-1), 1, ROW(B3))&"♂"&VLOOKUP(ROUNDDOWN(
 SEQUENCE(MAX(IF(A3:E<>"", 1)*ROW(B3:B))-(ROW(B3)-1), COLUMNS(A:E)*G3, COLUMNS(A:E)*G3)/COLUMNS(A:E))-
 SEQUENCE(MAX(IF(A3:E<>"", 1)*ROW(A3:A))-(ROW(A3)-1), 1, 0)*(G3-1)&"♀"&
 SEQUENCE(MAX(IF(A3:E<>"", 1)*ROW(A3:A))-(ROW(A3)-1), COLUMNS(A:E)*G3)-(ROUNDDOWN(
 SEQUENCE(1, COLUMNS(A:E)*G3, 0)/COLUMNS(A:E))*COLUMNS(A:E))-(COLUMNS(A:E)*G3*
 SEQUENCE(MAX(IF(A3:E<>"", 1)*ROW(A3:A))-(ROW(A3)-1), 1, 0)), 
 QUERY(SPLIT(FLATTEN(IF(A3:E="",,ROW(B3:B)&"♀"&COLUMN(A:E)-(COLUMN(A3)-1)&"×"&A3:E)), "×"), 
 "where Col2 is not null", 0), 2, 0))), 9^9, 2, 1, 1), "♂"), 
 "select count(Col2) where Col1 is not null group by Col1 label count(Col2)''"))

---

---

=ARRAYFORMULA(QUERY(SPLIT(SORTN(FLATTEN(IFNA(
 SEQUENCE(MAX(IF(B3:U<>"", 1)*ROW(B3:B))-(ROW(B3)-1), 1, ROW(B3))&"♂"&VLOOKUP(ROUNDDOWN(
 SEQUENCE(MAX(IF(B3:U<>"", 1)*ROW(B3:B))-(ROW(B3)-1), COLUMNS(B:U)*W3, COLUMNS(B:U)*W3)/COLUMNS(B:U))-
 SEQUENCE(MAX(IF(B3:U<>"", 1)*ROW(B3:B))-(ROW(B3)-1), 1, 0)*(W3-1)&"♀"&
 SEQUENCE(MAX(IF(B3:U<>"", 1)*ROW(B3:B))-(ROW(B3)-1), COLUMNS(B:U)*W3)-(ROUNDDOWN(
 SEQUENCE(1, COLUMNS(B:U)*W3, 0)/COLUMNS(B:U))*COLUMNS(B:U))-(COLUMNS(B:U)*W3*
 SEQUENCE(MAX(IF(B3:U<>"", 1)*ROW(B3:B))-(ROW(B3)-1), 1, 0)), 
 QUERY(SPLIT(FLATTEN(IF(B3:U="",,ROW(B3:B)&"♀"&COLUMN(B:U)-(COLUMN(B3)-1)&"×"&B3:U)), "×"), 
 "where Col2 is not null", 0), 2, 0))), 9^9, 2, 1, 1), "♂"), 
 "select count(Col2) where Col1 is not null group by Col1 label count(Col2)''"))

---

---

update:

=ARRAYFORMULA(QUERY(SPLIT(SORTN(FLATTEN(IFNA(
SEQUENCE(MAX(IF(INDIRECT(Y3&X3&":"&Z3)<>"", 1)*ROW(INDIRECT(Y3&X3&":"&Y3)))-(ROW(INDIRECT(Y3&X3))-1), 1, ROW(INDIRECT(Y3&X3)))&"♂"&VLOOKUP(ROUNDDOWN(
SEQUENCE(MAX(IF(INDIRECT(Y3&X3&":"&Z3)<>"", 1)*ROW(INDIRECT(Y3&X3&":"&Y3)))-(ROW(INDIRECT(Y3&X3))-1), COLUMNS(INDIRECT(Y3&":"&Z3))*W3, COLUMNS(INDIRECT(Y3&":"&Z3))*W3)/COLUMNS(INDIRECT(Y3&":"&Z3)))-
SEQUENCE(MAX(IF(INDIRECT(Y3&X3&":"&Z3)<>"", 1)*ROW(INDIRECT(Y3&X3&":"&Y3)))-(ROW(INDIRECT(Y3&X3))-1), 1, 0)*(W3-1)&"♀"&
SEQUENCE(MAX(IF(INDIRECT(Y3&X3&":"&Z3)<>"", 1)*ROW(INDIRECT(Y3&X3&":"&Y3)))-(ROW(INDIRECT(Y3&X3))-1), COLUMNS(INDIRECT(Y3&":"&Z3))*W3)-(ROUNDDOWN(
SEQUENCE(1, COLUMNS(INDIRECT(Y3&":"&Z3))*W3, 0)/COLUMNS(INDIRECT(Y3&":"&Z3)))*COLUMNS(INDIRECT(Y3&":"&Z3)))-(COLUMNS(INDIRECT(Y3&":"&Z3))*W3*
SEQUENCE(MAX(IF(INDIRECT(Y3&X3&":"&Z3)<>"", 1)*ROW(INDIRECT(Y3&X3&":"&Y3)))-(ROW(INDIRECT(Y3&X3))-1), 1, 0)), 
QUERY(SPLIT(FLATTEN(IF(INDIRECT(Y3&X3&":"&Z3)="",,ROW(INDIRECT(Y3&X3&":"&Y3))&"♀"&COLUMN(INDIRECT(Y3&":"&Z3))-(COLUMN(INDIRECT(Y3&X3))-1)&"×"&INDIRECT(Y3&X3&":"&Z3))), "×"), 
"where Col2 is not null", 0), 2, 0))), 9^9, 2, 1, 1), "♂"), 
"select count(Col2) where Col1 is not null group by Col1 label count(Col2)''"))

---

---

---

full explanation of the formula & how it works