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

Copper Contributor

# 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 players occur based on 5 names I choose. See below.

4 Replies

# Re: Excel formula to count how many times the same 5 names show up with the same date. aka NBA lineu

@Hendew44  This should work (but there may be easier ways):

``````=LET(roster_list, B2:D31, name_list, G2:G6,
sorted_starters, BYCOL(WRAPCOLS(TAKE(roster_list,,-1),5),LAMBDA(a, TEXTJOIN(",",,SORT(a)))),
sorted_name_list, TEXTJOIN(",",,SORT(name_list)),
SUM(--(sorted_name_list=sorted_starters)))``````

EDIT: for example this is slightly more concise:

``````=LET(roster_list, B2:D31, name_list, G2:G6,
starters, WRAPROWS(TAKE(roster_list,,-1),5),
SUM(--(MMULT(--ISNUMBER(XMATCH(starters,name_list)),{1;1;1;1;1})=5)))``````

# Re: Excel formula to count how many times the same 5 names show up with the same date. aka NBA lineu

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)
)``````

# Re: Excel formula to count how many times the same 5 names show up with the same date. aka NBA lineu

=SUM(BYROW(WRAPROWS(IF(COUNTIF(E1:E5,C5:C500),A5:A500,0),5,0),LAMBDA(x,--AND(x=SEQUENCE(,5)))))

# Re: Excel formula to count how many times the same 5 names show up with the same date. aka NBA lineu

@Hendew44 Here's a couple more MS365 options to add to the mix...

Using a 2-dimensional COUNTIFS array (by player and date):

``=SUM(BYCOL(COUNTIFS(C5:C500, E5:E9, B5:B500, TOROW(UNIQUE(B5:B500))), LAMBDA(c, PRODUCT(c))))``

Or simplified with WRAPROWS (by player):

``=SUM(BYROW(COUNTIF(E5:E9, WRAPROWS(C5:C500, 5)), LAMBDA(r, PRODUCT(r))))``