Forum Discussion

Hendew44's avatar
Hendew44
Copper Contributor
Jan 18, 2024

Excel formula to count how many times the same 5 names show up with the same date. aka NBA lineup

There are 3 columns   a5:500 1-5 repeating every 6th row b5:500 Date which changes every 6th row c5:c500 Player names vary depending on who started.   I want find how many times the same 5 play...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Jan 18, 2024

Hendew44 

Try this one:

=LET(
    unique_dates, UNIQUE(Lineups[Date]),
    TotalStarters, LAMBDA(a, v,
        LET(
            game, FILTER(Lineups[Player], Lineups[Date] = v, 0),
            tally, IF(COUNT(XMATCH(starting, game)) = 5, 1, 0),
            SUM(a, tally)
        )
    ),
    REDUCE(0, unique_dates, TotalStarters)
)

 

Resources