count a certain combination in a sheet

[johanvl]

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-48-54-61-79-90)

Now my question:

How can I count how many times a given combination is in my sheet? (fe 33-54-79, or 61-90, or 15-48-79-90)

So fe 61-90 can appear in 30 columns

I have in a separate sheet 7 cells to fill in the values i need to find, so i can choose the combinations myself (from A1 to A7)

Thanks in advance

[#2]

@johanvl 

Let's say your data are in A1:GJH7 on Sheet1.

And the values you want to count are on Sheet2 in A1 and down.

Enter the following formula in A10 on Sheet1:

=AND(COUNTIF(A1:A7,Sheet2!$A$1:INDEX(Sheet2!$A$1:$A$7,COUNTA(Sheet2!$A$1:$A$7))))

Fill to the right to GJH10.

The formula to count the number of columns that contain the combination is

=COUNTIF(Sheet1!A10:GJH10,TRUE)

[#3]

@johanvl 

=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: