Shorten this formula

[Stephen_IV]

Is there a way to shorten this formula? I cannot figure it out. Any help would be appreciated!

=LET(
    unique_vals, UNIQUE(A2:A209),
    count_C, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((C2:C209="E") + (C2:C209="P"))))),
    count_D, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((D2:D209="E") + (D2:D209="P"))))),
    count_E, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((E2:E209="E") + (E2:E209="P"))))),
    count_F, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((F2:F209="E") + (F2:F209="P"))))),
    count_G, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((G2:G209="E") + (G2:G209="P"))))),
    count_H, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((H2:H209="E") + (H2:H209="P"))))),
    count_I, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((I2:I209="E") + (I2:I209="P"))))),
    count_J, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((J2:J209="E") + (J2:J209="P"))))),
    count_K, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((K2:K209="E") + (K2:K209="P"))))),
    count_L, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((L2:L209="E") + (L2:L209="P"))))),
    count_M, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((M2:M209="E") + (M2:M209="P"))))),
    count_N, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((N2:N209="E") + (N2:N209="P"))))),
    count_O, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((O2:O209="E") + (O2:O209="P"))))),
    count_P, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((P2:P209="E") + (P2:P209="P"))))),
    count_Q, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((Q2:Q209="E") + (Q2:Q209="P"))))),
    count_R, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((R2:R209="E") + (R2:R209="P"))))),
    count_S, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((S2:S209="E") + (S2:S209="P"))))),
    count_T, MAP(unique_vals, LAMBDA(val, SUMPRODUCT((A2:A209=val) * ((T2:T209="E") + (T2:T209="P"))))),
    HSTACK(unique_vals, count_C, count_D, count_E, count_F, count_G, count_H, count_I, count_J, count_K, count_L, count_M, count_N, count_O, count_P, count_Q, count_R, count_S, count_T)
)

 

[djclements]

A couple of possibilities:

=LET(
    a, A2:A209,
    u, UNIQUE(a),
    b, C2:T209,
    REDUCE(u,SEQUENCE(COLUMNS(b)),LAMBDA(p,c,HSTACK(p,BYROW(COUNTIFS(a,u,INDEX(b,,c),{"E","P"}),SUM))))
)

Or:

=LET(u,UNIQUE(A2:A209),HSTACK(u,MMULT(--(TOROW(A2:A209)=u),--((C2:T209="E")+(C2:T209="P")))))

 

[djclements]

Or, if you're feeling adventurous:

=LET(
    a, A2:A209,
    u, UNIQUE(a),
    fx, BYCOL((C2:T209="E")+(C2:T209="P"), LAMBDA(b, LAMBDA(x, SUM((a=x)*b)))),
    HSTACK(u, MAP(IF({1},u,fx), IF({1},fx,u), LAMBDA(v,λ, λ(v))))
)

But the first two are likely more efficient.

 

[Stephen_IV]

Fantastic!!!! I appreciate all of your help!!!! Works great!!! Thank you!!!

 

[djclements]

You're welcome! Happy to help!

Just for fun, here's another option using the new PIVOTBY function:

=LET(
    rcλ, LAMBDA(n,TOCOL(CHOOSE(n,A2:A209,SEQUENCE(,COLUMNS(C2:T209))))),
    DROP(PIVOTBY(rcλ({1}),rcλ({2}),TOCOL((C2:T209="E")+(C2:T209="P")),SUM,0,0,,0),1)
)

By default, PIVOTBY will sort by the first field. If you prefer it to remain in the same order as the source data, you could create a unique ID number using MATCH:

=LET(
    u, UNIQUE(A2:A209),
    rcλ, LAMBDA(n,TOCOL(CHOOSE(n,MATCH(A2:A209,u,0),SEQUENCE(,COLUMNS(C2:T209))))),
    HSTACK(u,DROP(PIVOTBY(rcλ({1}),rcλ({2}),TOCOL((C2:T209="E")+(C2:T209="P")),SUM,0,0,,0),1,1))
)

Cheers!

 

[djclements]

=LET(u,UNIQUE(A2:A209),HSTACK(u,MMULT(--(TOROW(A2:A209)=u),--((C2:T209="E")+(C2:T209="P")))))

A slight correction to the MMULT method in my first response, you can remove the double-negative notation from array2:

=LET(u,UNIQUE(A2:A209),HSTACK(u,MMULT(--(TOROW(A2:A209)=u),(C2:T209="E")+(C2:T209="P"))))

 

[Stephen_IV]

Thank you for these also!!!!

 

[djclements]

You're welcome again!

I should also correct a bit of a faux pas in the second example of post #5... the way it's written, if range A2:A209 contains any blank cells, MATCH will return #N/A for each blank item, which will cause the UNIQUE list and the PIVOTBY results to be out of sync. XMATCH would be more appropriate for this scenario because it will return the correct unique ID number for each blank item as expected.