Forum Discussion
count a certain combination in a sheet
Hello, I got problems to solve next thing: I have a sheet, 5000 columns, 7 rows. Each cell is filled with a number from 1 to 100 (both included). Each column has 7 unique ordered values (fe 15-33...
=TOCOL(TRANSPOSE(Sheet1!A1:GJH7))=SUM(BYROW(C1:C35000,LAMBDA(row,N(TEXTJOIN(",",,OFFSET(row,0,0,COUNTA(A1:A7),1))=TEXTJOIN(",",,OFFSET(A1,0,0,COUNTA(A1:A7),1))))))An alternative could be these formulas. The first formula is in cell C1 in Sheet2 and spills the columns down. The second formula is in cell D1 and returns the count.
Sheet1:
Sheet2: