counting combinations of 3's

[excelNewbie22]

hi, i got help here Excel Questions
regarding counting combinations of 2's,
now i need to do so for 3's, and 4's,
can someone help me modify it?

=LET(t,TOCOL(C2:H3),SUM(--(DROP(t,-1)&"-"&DROP(t,1)=C1:G1&"-"&D1:H1)))

and maybe explain it to me in a few words?

 

[excelNewbie22]

c-o-n-f-i-r-m-e-d !
learned something new, thank you!

 

[djclements]

Hi @coulombevin,

Just a heads up, by changing the final calculation of the LET formula shown in your mini-sheet from:

SUM(BYROW(uniqueTest,LAMBDA(r,IFERROR(ROWS(FILTER(allPossibilities,allPossibilities=r)),0))))

to:

SUM(BYROW(uniqueTest,LAMBDA(r,LAMBDA(t,SUM(IF(t="",0,ROWS(t))))(FILTER(allPossibilities,allPossibilities=r,"")))))

you've inadvertently caused the result to return ROWS(t)^2, because t (FILTER) returns an array object (TYPE 64), and when IF receives an array object in its logical_test argument, both the [value_if_true] and [value_if_false] arguments are resized via broadcasting to fill the same number of rows and/or columns. For example, when FILTER returns 2 rows of matching possibilities, t="" returns {FALSE;FALSE}, which causes IF to return {2;2}. You could replace SUM with the implicit intersection operator (@) to return the first element of the resulting array, but honestly the first version of your formula with IFERROR was just as good.

Another possible variation of the lambda function definition using similar logic could be:

=LAMBDA(test_row,array_to_test,number_chosen,
    LET(
        λ, LAMBDA(a,n,
            LET(
                w, COLUMNS(a),
                j, MIN(w,n),
                i, w-j+1,
                k, SEQUENCE(i)+SEQUENCE(,j,0),
                TEXTBEFORE(TEXTAFTER(BYROW(a,LAMBDA(r,TEXTJOIN("-",0,"",INDEX(r,k),""))),"-",SEQUENCE(,i,,j)),"-",j)
            )
        ),
        t, TOCOL(λ(test_row,number_chosen)),
        SUM(BYROW(λ(array_to_test,number_chosen),LAMBDA(r,SUM(N(t=r)))))
    )
)

Cheers!

 

[coulombevin]

Hi @djclements ,

That explains why I had everything with a power of 2 ... I tried so much to finaly end up with IFERRORS ...

Thanks for the explanation and for taking the time to develop this new formula,

Vincent