How to count the number of times a specific column is the maximum value in a row?-CodePudding

I have a spreadsheet that looks like this, which tracks attendance and order. On day 1, the order was [Alice, Bob, Catherine, Dave]. On day 2, the order was [Bob, Dave, Catherine], and Alice was absent:

Date	Alice	Bob	Catherine	Dave
10/1	0	1	2	3
10/2	x	0	2	1
10/3	3	1	2	0
10/4	1	0	x	x
10/5	0	x	1	2

I am trying to write a formula to get the total number of times each attendee went last. In other words, I want to count the number of times a name in a column is the MAX value for each date row, ignoring any x's. Ideally, I would like a single formula that I could place in a single cell. If successful the resulting table would look like this:

Attendee	# of times they went last
Alice	2
Bob	0
Catherine	1
Dave	2

What's the best way to accomplish this?

CodePudding user response：

Find the MAX BYROW, then compare the max to each of the Attendees using REDUCE OFFSET. If equal, create a SUM:

=LAMBDA(
  max,
  REDUCE(
    {"Attendee","#times"},
    B1:E1,
    LAMBDA(
      a,c,
      {a;c,SUMPRODUCT(OFFSET(c,1,0,5)=max)}
    )
  )
)(BYROW(B2:E6,LAMBDA(r,MAX(r))))

CodePudding user response：

try:

=INDEX(QUERY(BYROW(B2:INDEX(E:E, MAX((A:A<>"")*ROW(A:A))), 
 LAMBDA(x, TEXTJOIN(, 1, IF(x=MAX(x), B1:E1, )))), 
 "select Col1,count(Col1) group by Col1 label count(Col1)''"))

with Bob:

=SORTN({QUERY(BYROW(B2:INDEX(E:E, MAX((A:A<>"")*ROW(A:A))), 
 LAMBDA(x, TEXTJOIN(, 1, IF(x=MAX(x), B1:E1, )))), 
 "select Col1,count(Col1) group by Col1 label count(Col1)''"); 
 TRANSPOSE({B1:E1;(B1:E1="")*1})}, 9^9, 2, 1, 1)