Count the Number of Unique Sequences

[William53]

xl2bb.xlsx
	B	C	D	E	F	G	H
1	0-15	16-30	31-45	46-60	61-75	76-90
2	1	0	0	1	1	0	8
3	2	0	0	1	1	1	4
4	1	1	1	1	0	0	4
5	0	0	0	1	0	0	4
6	1	0	0	1	1	0	7
7	0	2	0	1	1	0	4
8	1	1	1	1	1	1	4
9	0	1	2	0	2	1	4
10	0	0	0	0	0	1	5
11	0	0	1	1	0	0	4
12	0	0	1	1	1	0	4
13	1	2	0	0	2	0	3
14	1	0	0	1	1	0	6
15	2	0	0	1	1	1	3
16	1	1	1	1	0	0	3
17	0	0	0	1	0	0	3
18	1	0	0	1	1	0	5
19	0	2	0	1	1	0	3
20	1	1	1	1	1	1	3
21	0	1	2	0	2	1	3
22	0	0	0	0	0	1	4
23	0	0	1	1	0	0	3
24	0	0	1	1	1	0	3
25	1	2	0	0	2	0	2
26	0	2	0	1	1	0	2
27	1	1	1	1	1	1	2
28	0	1	2	0	2	1	2
29	0	0	0	0	0	1	3
30	2	0	0	1	1	1	2
31	1	1	1	1	0	0	2
32	0	0	0	1	0	0	2
33	1	0	0	1	1	0	4
34	0	0	1	1	0	0	2
35	0	0	1	1	1	0	2
36	0	0	0	0	1	1	1
37	0	0	0	0	0	1	2
38	0	2	0	1	1	1	1
39	1	0	0	1	1	0	3
40	2	0	0	1	1	1	1
41	1	1	1	1	0	0	1
42	0	0	0	1	0	0	1
43	1	0	0	1	1	0	2
44	0	2	0	1	1	0	1
45	1	1	1	1	1	1	1
46	0	1	2	0	2	1	1
47	0	0	0	0	0	1	1
48	0	0	1	1	0	0	1
49	0	0	1	1	1	0	1
50	1	2	0	0	2	0	1
51	1	0	0	1	1	0	1
Goal Time

Cell Formulas
Range	Formula
H2:H51	H2	=SUM(--(MMULT(--(B2:G2=B2:G51),SEQUENCE(6,,,0))=6))

Kind Regards

William

 

[StephenCrump]

H2 should be: =SUM(--(MMULT(--(B2:G2=B$2:G$51),SEQUENCE(6,,,0))=6))

I think you want?

=ROWS(UNIQUE(B2:G51))
=13

 

[William53]

Hi Stephen
What you advised works really well and gives me the total unique rows. I played with the formula for a bit and used =Unique(B2:G51) and it actually gave me the 13 rows of unique sequences. What I would like to do but I cannot seem to find the right combination of formulae to do it, is to count each of those individual rows to find out how many times they occur in the main data?
Is this even possible?

Kind Regards
William

xl2bb.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	0-15	16-30	31-45	46-60	61-75	76-90
2	1	0	0	1	1	0	8
3	2	0	0	1	1	1	4		13
4	1	1	1	1	0	0	4
5	0	0	0	1	0	0	4
6	1	0	0	1	1	0	8		1	0	0	1	1	0
7	0	2	0	1	1	0	4		2	0	0	1	1	1
8	1	1	1	1	1	1	4		1	1	1	1	0	0
9	0	1	2	0	2	1	4		0	0	0	1	0	0
10	0	0	0	0	0	1	5		0	2	0	1	1	0
11	0	0	1	1	0	0	4		1	1	1	1	1	1
12	0	0	1	1	1	0	4		0	1	2	0	2	1
13	1	2	0	0	2	0	3		0	0	0	0	0	1
14	1	0	0	1	1	0	8		0	0	1	1	0	0
15	2	0	0	1	1	1	4		0	0	1	1	1	0
16	1	1	1	1	0	0	4		1	2	0	0	2	0
17	0	0	0	1	0	0	4		0	0	0	0	1	1
18	1	0	0	1	1	0	8		0	2	0	1	1	1
19	0	2	0	1	1	0	4
20	1	1	1	1	1	1	4
21	0	1	2	0	2	1	4
22	0	0	0	0	0	1	5
23	0	0	1	1	0	0	4
24	0	0	1	1	1	0	4
25	1	2	0	0	2	0	3
26	0	2	0	1	1	0	4
27	1	1	1	1	1	1	4
28	0	1	2	0	2	1	4
29	0	0	0	0	0	1	5
30	2	0	0	1	1	1	4
31	1	1	1	1	0	0	4
32	0	0	0	1	0	0	4
33	1	0	0	1	1	0	8
34	0	0	1	1	0	0	4
35	0	0	1	1	1	0	4
36	0	0	0	0	1	1	1
37	0	0	0	0	0	1	5
38	0	2	0	1	1	1	1
39	1	0	0	1	1	0	8
40	2	0	0	1	1	1	4
41	1	1	1	1	0	0	4
42	0	0	0	1	0	0	4
43	1	0	0	1	1	0	8
44	0	2	0	1	1	0	4
45	1	1	1	1	1	1	4
46	0	1	2	0	2	1	4
47	0	0	0	0	0	1	5
48	0	0	1	1	0	0	4
49	0	0	1	1	1	0	4
50	1	2	0	0	2	0	3
51	1	0	0	1	1	0	8
52
Goal Time

Cell Formulas
Range	Formula
J3	J3	=ROWS(UNIQUE(B2:G51))
J6:O18	J6	=UNIQUE(B2:G51)
H2:H51	H2	=SUM(--(MMULT(--(B2:G2=B$2:G$51),SEQUENCE(6,,,0))=6))
Dynamic array formulas.

 

[StephenCrump]

You could use a similar formula to your MMULT construction in Post #1.

Or another way:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		0-15	16-30	31-45	46-60	61-75	76-90
2		1	0	0	1	1	0	8
3		2	0	0	1	1	1	4
4		1	1	1	1	0	0	4
5		0	0	0	1	0	0	4
6		1	0	0	1	1	0	8		1	0	0	1	1	0	8
7		0	2	0	1	1	0	4		2	0	0	1	1	1	4
8		1	1	1	1	1	1	4		1	1	1	1	0	0	4
9		0	1	2	0	2	1	4		0	0	0	1	0	0	4
10		0	0	0	0	0	1	5		0	2	0	1	1	0	4
11		0	0	1	1	0	0	4		1	1	1	1	1	1	4
12		0	0	1	1	1	0	4		0	1	2	0	2	1	4
13		1	2	0	0	2	0	3		0	0	0	0	0	1	5
14		1	0	0	1	1	0	8		0	0	1	1	0	0	4
15		2	0	0	1	1	1	4		0	0	1	1	1	0	4
16		1	1	1	1	0	0	4		1	2	0	0	2	0	3
17		0	0	0	1	0	0	4		0	0	0	0	1	1	1
18		1	0	0	1	1	0	8		0	2	0	1	1	1	1
19		0	2	0	1	1	0	4
20		1	1	1	1	1	1	4
21		0	1	2	0	2	1	4
22		0	0	0	0	0	1	5
23		0	0	1	1	0	0	4
24		0	0	1	1	1	0	4
25		1	2	0	0	2	0	3
26		0	2	0	1	1	0	4
27		1	1	1	1	1	1	4
28		0	1	2	0	2	1	4
29		0	0	0	0	0	1	5
30		2	0	0	1	1	1	4
31		1	1	1	1	0	0	4
32		0	0	0	1	0	0	4
33		1	0	0	1	1	0	8
34		0	0	1	1	0	0	4
35		0	0	1	1	1	0	4
36		0	0	0	0	1	1	1
37		0	0	0	0	0	1	5
38		0	2	0	1	1	1	1
39		1	0	0	1	1	0	8
40		2	0	0	1	1	1	4
41		1	1	1	1	0	0	4
42		0	0	0	1	0	0	4
43		1	0	0	1	1	0	8
44		0	2	0	1	1	0	4
45		1	1	1	1	1	1	4
46		0	1	2	0	2	1	4
47		0	0	0	0	0	1	5
48		0	0	1	1	0	0	4
49		0	0	1	1	1	0	4
50		1	2	0	0	2	0	3
51		1	0	0	1	1	0	8
52
Sheet1

Cell Formulas
Range	Formula
J6:O18	J6	=UNIQUE(B2:G51)
P6:P18	P6	=SUM(--BYROW(B$2:G$51=J6:O6,LAMBDA(r,AND(r))))
H2:H51	H2	=SUM(--(MMULT(--(B2:G2=B$2:G$51),SEQUENCE(6,,,0))=6))
Dynamic array formulas.

 

[William53]

Many many thanks Stephen.
This worked perfectly and now I can analyse all my son's football matches to show when each of the results occur, especially goals conceded
I have never used LAMBDA before so researched it. I get the BYROW bit and that it uses the LAMBDA across the data to give a single answer and the SUM part adds them up.
What I don't get is the (r,AND(r) part of the function.
If it is not too much trouble, could you explain?

Kind Regards
William

 

[StephenCrump]

The two parts of the LAMBDA are:
1) The name I'm using to designate the row - I've used r, and
2) What I want to do with each row, r.

Here's a simpler example which just calculates row totals:

	A	B	C	D	E	F
1
2		1	2	3	6
3		4	5	6	15
4		7	8	9	24
5
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=BYROW(B2:D4,LAMBDA(MyRow,SUM(MyRow)))
Dynamic array formulas.

And here's the formula from Post #4 broken down into steps:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1									Test
2									1	0	0	1	1	0		8	<-- In one step
3																8	<-- Step3 - Add the TRUEs
4
5		0-15	16-30	31-45	46-60	61-75	76-90		Step1 - compare all 50							Step2 - check whole row matches
6		1	0	0	1	1	0		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		TRUE
7		2	0	0	1	1	1		FALSE	TRUE	TRUE	TRUE	TRUE	FALSE		FALSE
8		1	1	1	1	0	0		TRUE	FALSE	FALSE	TRUE	FALSE	TRUE		FALSE
9		0	0	0	1	0	0		FALSE	TRUE	TRUE	TRUE	FALSE	TRUE		FALSE
10		1	0	0	1	1	0		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		TRUE
11		0	2	0	1	1	0		FALSE	FALSE	TRUE	TRUE	TRUE	TRUE		FALSE
12		1	1	1	1	1	1		TRUE	FALSE	FALSE	TRUE	TRUE	FALSE		FALSE
13		0	1	2	0	2	1		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		FALSE
14		0	0	0	0	0	1		FALSE	TRUE	TRUE	FALSE	FALSE	FALSE		FALSE
15		0	0	1	1	0	0		FALSE	TRUE	FALSE	TRUE	FALSE	TRUE		FALSE
16		0	0	1	1	1	0		FALSE	TRUE	FALSE	TRUE	TRUE	TRUE		FALSE
17		1	2	0	0	2	0		TRUE	FALSE	TRUE	FALSE	FALSE	TRUE		FALSE
18		1	0	0	1	1	0		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		TRUE
19		2	0	0	1	1	1		FALSE	TRUE	TRUE	TRUE	TRUE	FALSE		FALSE
20		1	1	1	1	0	0		TRUE	FALSE	FALSE	TRUE	FALSE	TRUE		FALSE
21		0	0	0	1	0	0		FALSE	TRUE	TRUE	TRUE	FALSE	TRUE		FALSE
22		1	0	0	1	1	0		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		TRUE
23		0	2	0	1	1	0		FALSE	FALSE	TRUE	TRUE	TRUE	TRUE		FALSE
24		1	1	1	1	1	1		TRUE	FALSE	FALSE	TRUE	TRUE	FALSE		FALSE
25		0	1	2	0	2	1		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		FALSE
26		0	0	0	0	0	1		FALSE	TRUE	TRUE	FALSE	FALSE	FALSE		FALSE
27		0	0	1	1	0	0		FALSE	TRUE	FALSE	TRUE	FALSE	TRUE		FALSE
28		0	0	1	1	1	0		FALSE	TRUE	FALSE	TRUE	TRUE	TRUE		FALSE
29		1	2	0	0	2	0		TRUE	FALSE	TRUE	FALSE	FALSE	TRUE		FALSE
30		0	2	0	1	1	0		FALSE	FALSE	TRUE	TRUE	TRUE	TRUE		FALSE
31		1	1	1	1	1	1		TRUE	FALSE	FALSE	TRUE	TRUE	FALSE		FALSE
32		0	1	2	0	2	1		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		FALSE
33		0	0	0	0	0	1		FALSE	TRUE	TRUE	FALSE	FALSE	FALSE		FALSE
34		2	0	0	1	1	1		FALSE	TRUE	TRUE	TRUE	TRUE	FALSE		FALSE
35		1	1	1	1	0	0		TRUE	FALSE	FALSE	TRUE	FALSE	TRUE		FALSE
36		0	0	0	1	0	0		FALSE	TRUE	TRUE	TRUE	FALSE	TRUE		FALSE
37		1	0	0	1	1	0		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		TRUE
38		0	0	1	1	0	0		FALSE	TRUE	FALSE	TRUE	FALSE	TRUE		FALSE
39		0	0	1	1	1	0		FALSE	TRUE	FALSE	TRUE	TRUE	TRUE		FALSE
40		0	0	0	0	1	1		FALSE	TRUE	TRUE	FALSE	TRUE	FALSE		FALSE
41		0	0	0	0	0	1		FALSE	TRUE	TRUE	FALSE	FALSE	FALSE		FALSE
42		0	2	0	1	1	1		FALSE	FALSE	TRUE	TRUE	TRUE	FALSE		FALSE
43		1	0	0	1	1	0		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		TRUE
44		2	0	0	1	1	1		FALSE	TRUE	TRUE	TRUE	TRUE	FALSE		FALSE
45		1	1	1	1	0	0		TRUE	FALSE	FALSE	TRUE	FALSE	TRUE		FALSE
46		0	0	0	1	0	0		FALSE	TRUE	TRUE	TRUE	FALSE	TRUE		FALSE
47		1	0	0	1	1	0		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		TRUE
48		0	2	0	1	1	0		FALSE	FALSE	TRUE	TRUE	TRUE	TRUE		FALSE
49		1	1	1	1	1	1		TRUE	FALSE	FALSE	TRUE	TRUE	FALSE		FALSE
50		0	1	2	0	2	1		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		FALSE
51		0	0	0	0	0	1		FALSE	TRUE	TRUE	FALSE	FALSE	FALSE		FALSE
52		0	0	1	1	0	0		FALSE	TRUE	FALSE	TRUE	FALSE	TRUE		FALSE
53		0	0	1	1	1	0		FALSE	TRUE	FALSE	TRUE	TRUE	TRUE		FALSE
54		1	2	0	0	2	0		TRUE	FALSE	TRUE	FALSE	FALSE	TRUE		FALSE
55		1	0	0	1	1	0		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE		TRUE
56
Sheet1

Cell Formulas
Range	Formula
P2	P2	=SUM(--BYROW(B$6:G$55=I2:N2,LAMBDA(r,AND(r))))
P3	P3	=SUM(--P6:P55)
I6:N55	I6	=B$6:G$55=I2:N2
P6:P55	P6	=AND(I6:N6)
Dynamic array formulas.

 

[William53]

Hi Stephen
Thanks for the extra information, very informative to my novice ability.
I really appreciate the extra time taken.
Not sure if I should mark it as a solution, please advise.
Kind Regards
William